Yet another SQL Server enthusiast

2015-04-09

How to get full path of every XML element using XQuery and recursive Common Table Expressions

Filed under: sql server,xml,xquery — sa @ 6:36 AM

Solution:

Code Snippet
DECLARE @x XML = N'
<A>
    <B>
        <C>Text1</C>
        <D>Text2</D>
        <E>
            <F>Text3</F>
        </E>
    </B>
    <G>Text4</G>
</A>';

  WITH XmlRec
  AS (
    SELECT    a.XmlCol.query('.') AS CurrentElement,
            a.XmlCol.value('(text())[1]', 'NVARCHAR(100)') AS CurrentElementValue,
            a.XmlCol.value('local-name(.)', 'SYSNAME') AS CurrentElementName,
            CONVERT(NVARCHAR(4000), a.XmlCol.value('local-name(.)', 'SYSNAME')) AS CurrentElementPath
    FROM    @x.nodes('*') a(XmlCol)
    UNION ALL
    SELECT    b.XmlCol.query('.') AS CurrentElement,
            b.XmlCol.value('(text())[1]', 'NVARCHAR(100)') AS CurrentElementValue,
            b.XmlCol.value('local-name(.)', 'SYSNAME') AS CurrentElementName,
            rec.CurrentElementPath + '/' + CONVERT(NVARCHAR(4000), b.XmlCol.value('local-name(.)', 'SYSNAME')) AS CurrentElementPath
    FROM    XmlRec rec
    CROSS APPLY rec.CurrentElement.nodes('*/*') b(XmlCol)
  )
  SELECT    r.*
  FROM        XmlRec r
  OPTION (MAXRECURSION 100)

Results:

XML.Element.Full.Path

Advertisements

1 Comment »

  1. I was looking rendering key-value pair. This should help me. Thanks!

    Comment by Rajesh Patel — 2017-05-06 @ 9:11 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: