Stored Procedure to insert data from XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure to insert data from XML

Author

Topic

arvidb
Starting Member

7 Posts

Posted - 2011-03-01 : 14:23:20

Dear friends!

Would it be possible to write a stored procedure that takes XML as input and inserts data to the database where the table and columns are declared in the XML, such that:

<InputData Table="TableX">
<Data Column="C1" DataType="int" Value="1">
<Data Column="C2" DataType="float" Value="2.23">
<Data Column="C3" DataType="varchar(32)" Value="Short text">
<Data Column="C4" DataType="varchar(MAX)" Value="Long text...">
</<InputData>

should do
INSERT INTO TableX (C1, C2, C3, C4) VALUES 1, 2.23, 'Short text', 'Long text...'

and

<InputData Table="TableY">
<Data Column="CN" DataType="int" Value="100">
</<InputData>

should do
INSERT INTO TableY (CN) VALUES 100

All help appreciated!

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-01 : 15:19:55

well your "Data Column" value should really be a tag...and XML values don't have data types


DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<ROOT>
<TableX C1="1" C2="2.33" C3="Short text" C4="Long Text..." >
</TableX>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/TableX',3)
         WITH (	C1  int,
				C2  float,
				C3  varchar(32),
				C4  varchar(max))

EXEC sp_xml_removedocument @idoc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

arvidb
Starting Member

7 Posts

Posted - 2011-03-01 : 16:36:02

quote:
Originally posted by X002548

well your "Data Column" value should really be a tag...and XML values don't have data types

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='<ROOT>
<TableX C1="1" C2="2.33" C3="Short text" C4="Long Text..." >
</TableX>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/TableX',3)
WITH ( C1 int,
C2 float,
C3 varchar(32),
C4 varchar(max))

EXEC sp_xml_removedocument @idoc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Well, I think you misunderstood my question. I want a more general stored procedure, that is why table, columns and data types are declared within the XML. Perhaps data type is unnecessary if it is possible to receive data type directly from the database using table and column.

BTW the Data tags should be closed.
<DATA Column="C1" DataType="int" Value="1" />

Thanks anyway!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources