XML to SQL - SQL Server

Question

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)

Siddharth Shah · Accepted Answer · 2018-07-02 11:55:53Z

4

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.

answered Jul 2, 2018 at 11:55

Siddharth Shah

798 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gottfried Lesigang Over a year ago

FROM OPENXML together with the stored procedures to prepare and to remove a document is outdated and should not be used any more...

Gottfried Lesigang Over a year ago

Btw: It is a rather bad idea to use backward navigation like with ../../../...

Gottfried Lesigang · Accepted Answer · 2018-07-02 11:28:13Z

3

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

answered Jul 2, 2018 at 11:28

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

XML to SQL - SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related