how can i read xml values and store that values into my database using sql server stored procedure

Question

0.00/5 (No votes)

See more:

Want to get values Coming in XML format using stored procedure. The values i am getting are like in the format as mentioned below . What i am trying to do is to just create a table in database as 'RecordsDB' and using Stored procedure parsing the XML, inserts the value in 'Records'. I am trying to search question like this on stack overflow and net but not getting answer ,only knows the mini way is to read the node value like line.column.value(something). But i do have some thousands of records coming from ASP.NET web service.*

<Root>                        (Root node)
  <Record>                    (Secondary node)
    <Name> A </Name>           (Child node1)
     <Age> 21 </Age>            (Child node2)
  </Record>
    <Record>
    <Name> b </Name>
     <Age> 22 </Age>
  </Record>
 </Root>

How to meet the task? Thanks.

Posted 26-Aug-13 7:20am

Rambo_Raja

Updated 26-Aug-13 7:25am

Mahesh Bailwal

v3

Add a Solution

Comments

Mahesh Bailwal 26-Aug-13 13:28pm

You want to write whole logic in Stored Procedure?

Rambo_Raja 26-Aug-13 13:33pm

Yes i want whole logic to be implemented in sql stored procedure. I am calling web service also from stored procedure and getting result in @response object in Stored procedure.

Mahesh Bailwal 26-Aug-13 14:14pm

Check OpenXML link below
http://technet.microsoft.com/en-us/library/ms187897.aspx

Mahesh Bailwal 26-Aug-13 13:31pm

Check openxml on link below

http://technet.microsoft.com/en-us/library/ms187897.aspx

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-08-26T20:07:00

U can use the inbuilt Stored Procedure...

SQL

DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<root>                        
<record>               
    <name> A </name>     
    <age> 21 </age>     
</record>
<record>
    <name> b </name>
    <age> 22 </age>
</record>
</root>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@idoc, '/Root/Record',2) WITH (Name  varchar(10),Age Int);

Result:

SQL

Name    Age
----   -----
 A      21
 b      22

how can i read xml values and store that values into my database using sql server stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

how can i read xml values and store that values into my database using sql server stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us