CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
KMittal
KMittal

Reputation: 602

XML to SQL - SQL Server

I'm traversing an XML file to read nodes and fill into to SQL Server tables. I have a Root node having Department node which further may have one or more as element. I want to select all the possible values from in a SQL result set.

Please find below XML I'm referring:

DECLARE @x XML='
<Root>
<Department>
<DeptID>D101</DeptID>
<DeptID>D102</DeptID>
</Department>
</Root>'

I'm using below SQL Query to get the data from XML but I can read only first DeptID as I'm passing [1] inside DeptID[1]. If I pass [2] I can get thee second value. But in real life scenario, I won't be able to know how many DeptID would be there in the XML. So I want a generic script to read as many as DeptIDs comes in XML.

SELECT  n.value('DeptID[1]','varchar(10)') AS DeptID FROM @x.nodes('/Root/Department') R(n)

Upvotes: 2

Views: 295

Answers (2)

Siddharth Shah
Siddharth Shah

Reputation: 79

You can use OpenXMl method of sql server to get more elements in table as follows.

Step 1: Suppose this is your sample XML data.

DECLARE @XML XML='
<ROOT>
<Customers>
<Customer CustomerID="C001" CustomerName="Arshad Ali">
<Orders>
<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
<OrderDetail ProductID="10" Quantity="5" />
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
</Orders>
<Address> Address line 1, 2, 3</Address>
</Customer>
<Customer CustomerID="C002" CustomerName="Paul Henriot">
<Orders>
<Order OrderID="10245" OrderDate="2011-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
</Orders>
<Address> Address line 5, 6, 7</Address>
</Customer>
<Customer CustomerID="C003" CustomerName="Carlos Gonzlez">
<Orders>
<Order OrderID="10283" OrderDate="2012-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3" />
</Order>
</Orders>
<Address> Address line 1, 4, 5</Address>
</Customer>
</Customers>
</ROOT>'

Step 2: Use of OPENXML method to get elements at any level as follows.

DECLARE @hDoc AS INT, @SQL NVARCHAR (MAX)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH 
(
CustomerID [varchar](50) '../../../@CustomerID',
CustomerName [varchar](100) '../../../@CustomerName',
Address [varchar](100) '../../../Address',
OrderID [varchar](1000) '../@OrderID',
OrderDate datetime '../@OrderDate',
ProductID [varchar](50) '@ProductID',
Quantity int '@Quantity'
)

EXEC sp_xml_removedocument @hDoc
GO

Above steps will give you following Output.

xml_data_to_table

Upvotes: 4

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this

DECLARE @x XML='
<Root>
<Department>
<DeptID>D101</DeptID>
<DeptID>D102</DeptID>
</Department>
</Root>';

SELECT d.value('text()[1]','varchar(10)') AS DeptID
FROM @x.nodes('/Root/Department/DeptID') A(d);

Your own code

SELECT  n.value('DeptID[1]','varchar(10)') AS DeptID 
FROM @x.nodes('/Root/Department') R(n)

... follows the right idea. But .nodes() must return the repeating element, which is <DeptID>. Your approach is looking for the first <DeptID> within <Department> actually

Upvotes: 3

Related Questions