T-SQL looping through XML data column to derive unique set of paths

Question

I have XML data column which contains question and answer as part of an application process. What I am trying to achieve through T-SQL/ Dynamic SQL is to derive a unique set of path wherever there is a target tag. So for the below xml example, I would expect something like

log/clients/client/section/questions/groupone/question/target log/clients/client/section/questions/grouptwo/question/target

Idea is to then to use this and loop through the XML to derive the values of the desired tags. I.e

[DATA].value('(/log/clients/client/section/questions/groupone/question/target', 'NVARCHAR(MAX)')

Problem is each application has different set of questions and xml structure i.e. some might have more questions, some might have different grouping. However all I want is if there is a tag then what is its path.

How can I best achieve this?

 
  
   
    
       what is your name
        
         1
         def

Gottfried Lesigang · Accepted Answer

The outdated approach with FROM OPENXML might be an option here. Check this answer.

At this link you'll find a function John Cappelletti posted from time to time, which will shred any XML (credits below the function's code).

But I'm not sure, what you are really trying to achieve... Why do you need the path? If you are interested in the values of all target nodes you might do something like this (deep search with // does not need the exact XPath)

 SELECT t.value(N'(text())[1]','nvarchar(max)')
 FROM @xml.nodes('//target') AS A(t);

If you really need all and everything you can check this:

CREATE FUNCTION [dbo].[udf-XML-Hier](@XML xml)

Returns Table 
As Return

with  cte0 as ( 
                  Select Lvl       = 1
                        ,ID        = Cast(1 as int) 
                        ,Pt        = Cast(NULL as int)
                        ,Element   = x.value('local-name(.)','varchar(150)')
                        ,Attribute = cast('' as varchar(150))
                        ,Value     = x.value('text()[1]','varchar(max)')
                        ,XPath     = cast(concat(x.value('local-name(.)','varchar(max)'),'[' ,cast(Row_Number() Over(Order By (Select 1)) as int),']') as varchar(max))
                        ,Seq       = cast(1000000+Row_Number() over(Order By (Select 1)) as varchar(max))
                        ,AttData   = x.query('.') 
                        ,XMLData   = x.query('*') 
                  From   @XML.nodes('/*') a(x) 
                  Union  All
                  Select Lvl       = p.Lvl + 1 
                        ,ID        = Cast( (Lvl + 1) * 1024 + (Row_Number() Over(Order By (Select 1)) * 2) as int ) * 10
                        ,Pt        = p.ID
                        ,Element   = c.value('local-name(.)','varchar(150)')
                        ,Attribute = cast('' as varchar(150))
                        ,Value     = cast( c.value('text()[1]','varchar(max)') as varchar(max) ) 
                        ,XPath     = cast(concat(p.XPath,'/',c.value('local-name(.)','varchar(max)'),'[',cast(Row_Number() Over(PARTITION BY c.value('local-name(.)','varchar(max)') Order By (Select 1)) as int),']') as varchar(max) )
                        ,Seq       = cast(concat(p.Seq,' ',10000000+Cast( (Lvl + 1) * 1024 + (Row_Number() Over(Order By (Select 1)) * 2) as int ) * 10) as varchar(max))
                        ,AttData   = c.query('.') 
                        ,XMLData   = c.query('*') 
                  From   cte0 p 
                  Cross  Apply p.XMLData.nodes('*') b(c) 
              )
    , cte1 as (   
                  Select R1 = Row_Number() over (Order By Seq),A.*
                  From  (
                          Select  Lvl,ID,Pt,Element,Attribute,Value,XPath,Seq From cte0
                          Union All
                          Select Lvl       = p.Lvl+1
                                ,ID        = p.ID + Row_Number() over (Order By (Select NULL)) 
                                ,Pt        = p.ID
                                ,Element   = p.Element
                                ,Attribute = x.value('local-name(.)','varchar(150)')
                                ,Value     = x.value('.','varchar(max)')
                                ,XPath     = p.XPath + '/@' + x.value('local-name(.)','varchar(max)')
                                ,Seq       = cast(concat(p.Seq,' ',10000000+p.ID + Row_Number() over (Order By (Select NULL)) ) as varchar(max))
                          From   cte0 p 
                          Cross  Apply AttData.nodes('/*/@*') a(x) 
                        ) A 
               )

Select A.R1
      ,R2  = IsNull((Select max(R1) From cte1 Where Seq Like A.Seq+'%'),A.R1)
      ,A.Lvl
      ,A.ID
      ,A.Pt
      ,A.Element
      ,A.Attribute
      ,A.XPath
      ,Title = Replicate('|---',Lvl-1)+Element+IIF(Attribute='','','@'+Attribute)
      ,A.Value
 From  cte1 A

/*
Source: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

Taken from John Cappelletti: https://stackoverflow.com/a/42729851/5089204

Declare @XML xml='AnnabelleSmith'
Select * from [dbo].[udf-XML-Hier](@XML) Order by R1
*/
GO 

DECLARE @xml XML=
'
  
   
     
     
      
       
        Age
       
       
         Height
       
       
         Weight
       
      
      
       what is your name
        
         1
         def
        
      
     
    
   
  
 ';

 SELECT * FROM dbo.[udf-XML-Hier](@xml);
GO

T-SQL looping through XML data column to derive unique set of paths

Answers (1)

Related Questions