Break down XML files using SQL Server into individual rows

Question

Here's what I'm doing. I'm inserting an XML file into an XML column, along with other columns such as date stamps etc. for logging (those are irrelevant to this request)

The file looks like something similar to this:

<topLevelItem>
  <secondLevelItem>
    <secondLevelItemDetail>
    </secondLevelItemDetail>
    <secondLevelItemAnotherDetail>
    </secondLevelItemAnotherDetail>
  </secondLevelItem>
  <secondLevelItem>
    <secondLevelItemDetail>
    </secondLevelItemDetail>
    <secondLevelItemAnotherDetail>
    </secondLevelItemAnotherDetail>
  </secondLevelItem>
  <secondLevelItem>
    <secondLevelItemDetail>
    </secondLevelItemDetail>
    <secondLevelItemAnotherDetail>
    </secondLevelItemAnotherDetail>
  </secondLevelItem>
<topLevelItem>

My goal is to be able to query the XML field and get a result set with each <secondLevelItem> in it's own row, in it's XML form. Such as below:

Row 1:

  <secondLevelItem>
    <secondLevelItemDetail>
    </secondLevelItemDetail>
    <secondLevelItemAnotherDetail>
    </secondLevelItemAnotherDetail>
  </secondLevelItem>

Row 2:

  <secondLevelItem>
    <secondLevelItemDetail>
    </secondLevelItemDetail>
    <secondLevelItemAnotherDetail>
    </secondLevelItemAnotherDetail>
  </secondLevelItem>

Row 3:

  <secondLevelItem>
    <secondLevelItemDetail>
    </secondLevelItemDetail>
    <secondLevelItemAnotherDetail>
    </secondLevelItemAnotherDetail>
  </secondLevelItem>

This has to be done using Microsoft SQL Server and without using CLI. I need to be able to declare which XML node I want the break down to start at, as some files have additional XML elements that I don't require. So something like, WHERE node = secondLevelItem.

marc_s · Accepted Answer · 2012-01-10 05:59:57Z

3

With SQL Server 2005 and newer, there's excellent XQuery support in SQL Server.

This approach here would be using the .nodes() and .query() method:

SELECT 
    Col.query('.')
FROM 
dbo.YourTable
CROSS APPLY
    YourXmlColumn.nodes('/topLevelItem/secondLevelItem') AS Tbl(Col)
WHERE
    (some condition)

The .nodes() function creates a "temporary, inline" pseudo table Tbl with a single column Col which contains one XML entry for each node in your XML column that matches the XPath given (in this case: each <secondLevelItem> inside the <topLevelItem> will be matched).

Since you want the entire XML for each of those XML elements, just use the .query('.') to return that complete XML element.

answered Jan 10, 2012 at 5:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

Mikael Eriksson · Accepted Answer · 2012-01-10 06:37:00Z

2

Use nodes() to shred your XML and use local-name() and sql:variable() to find the node you want, retrieve the XML with query().

declare @NodeName varchar(100) = 'secondLevelItem'

select T.N.query('.')
from YourTable
  cross apply XMLCol.nodes('//*[local-name()=sql:variable("@NodeName")]') as T(N)

answered Jan 10, 2012 at 6:37

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

James Hawks · Accepted Answer · 2012-01-10 08:34:26Z

If you are just supplying the original XML to a stored procedure and not pulling it from a table you could do the following.

DECLARE @XML xml
SET @XML = '<topLevelItem>
  <secondLevelItem>
    <secondLevelItemDetail>One</secondLevelItemDetail>
    <secondLevelItemAnotherDetail>One One</secondLevelItemAnotherDetail>
  </secondLevelItem>
  <secondLevelItem>
    <secondLevelItemDetail>Two</secondLevelItemDetail>
    <secondLevelItemAnotherDetail>Two Two</secondLevelItemAnotherDetail>
  </secondLevelItem>
  <secondLevelItem>
    <secondLevelItemDetail>Three</secondLevelItemDetail>
    <secondLevelItemAnotherDetail>Three Three</secondLevelItemAnotherDetail>
  </secondLevelItem>
</topLevelItem>'


SELECT 
    Tbl.Col.query('.')
FROM 
    @XML.nodes('/topLevelItem/secondLevelItem') AS Tbl(Col)

Collectives™ on Stack Overflow

Break down XML files using SQL Server into individual rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related