Parse XML file using SQL Server, and return child node section as XML

Question

I have an XML file that looks like this:

<NodeA Type="A" Date="2015-10-28">
  <NodeB Amount="0.00">
    <Items />
  </NodeB>
  <NodeB Amount="0.00">
    <Items>
      <Item code="2" val="50.00" />
      <Item code="8" val="50.00" />
    </Items>
  </NodeB>
</NodeA>

I am trying to use SQL Server and XPath to extract the child nodes of NodeA as XML.

Based on the above XML, I need to return a table with 2 rows, containing the following fields:

Type (nvarchar) | Date (Date) | ChildNode (XML)
-----------------------------------------------
A                 2015-10-28    <NodeB Amount="0.00"><Items /></NodeB>
A                 2015-10-28    <NodeB Amount="0.00"><Items>...</NodeB>

I know I can do this using C#, but is there a way to do this using XPath? I've some success with xPath returning the field values from NodeA, but can't seem to get NodeB to display as XML.

Krishnraj Rana Krishnraj Rana · Accepted Answer · 2015-12-10T15:39:02

Try this -

DECLARE @xml AS XML

SET @xml = '<NodeA Type="A" Date="2015-10-28">
  <NodeB Amount="0.00">
    <Items />
  </NodeB>
  <NodeB Amount="0.00">
    <Items>
      <Item code="2" val="50.00" />
      <Item code="8" val="50.00" />
    </Items>
  </NodeB>
</NodeA>'

SELECT c.value('(/NodeA/@Type)[1]', 'varchar(50)') AS Type, 
    c.value('(/NodeA/@Date)[1]', 'varchar(50)') AS Date,
    c.query('.') AS ChildNodeXML 
FROM @xml.nodes('/NodeA/NodeB') as T(C)

Result

Type   Date          ChildNodeXML
----   ------        ----------------------------------
A      2015-10-28   <NodeB Amount="0.00"><Items /></NodeB>
A      2015-10-28   <NodeB Amount="0.00"><Items><Item code="2" val="50.00" /><Item code="8" val="50.00" /></Items></NodeB>

Parse XML file using SQL Server, and return child node section as XML

1 Answers