How to get XML values into table with dynamic query? Column name or node name are not fix in XML

Question

DECLARE  @XML1 XML =
'<sender id="1" operation="NewValue" LastName="XYZ" loginId="10029" />
<sender id="2" operation="OldValue" LastName="PQR" loginId="10029" />'

SELECT T.C.value('local-name(.)', 'varchar(50)') AS NodeName,
    T.C.value('(.)[1]', 'varchar(50)') AS NodeValue
FROM @xml1.nodes('//*') AS T(C)

From OP's comment

One more example

DECLARE @xml XML = 
'<row> 
 <operation>NewValue</operation> 
 <LastName>Gandhi</LastName> 
 <loginId>1011</loginId> 
 </row> 
 <row> 
 <operation>OldValue</operation> 
 <LastName>Gandhi</LastName> 
 <loginId>1010</loginId> 
 </row>' 

SELECT T.C.value('local-name(.)', 'varchar(50)') AS NodeName, 
       T.C.value('(.)[1]', 'varchar(50)') AS NodeValue 
FROM @xml.nodes('row/*') AS T(C) 
WHERE T.C.value('(//row/operation/text())[1]', 'VARCHAR(20)') = 'NewValue'

(I want to get result like this query but my XML format is different).

Answer 1

Shnugo (on another post) suggested that post a Table-Valued-Function used to parse virtually any XML structure. I should add performance is respectable (90 ms for Example 1), but native XML would be more efficient.

The original source was: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx I only made a few tweaks.

Example 1

Declare @XML xml ='
<row> 
 <operation>NewValue</operation> 
 <LastName>Gandhi</LastName> 
 <loginId>1011</loginId> 
 </row> 
 <row> 
 <operation>OldValue</operation> 
 <LastName>Gandhi</LastName> 
 <loginId>1010</loginId> 
 </row>' 

Select * from [dbo].[udf-XML-Hier](@XML) Order by R1

Returns

Example 2

Declare @XML1 xml = '
<sender id="1" operation="NewValue" LastName="XYZ" loginId="10029" />
<sender id="2" operation="OldValue" LastName="PQR" loginId="10029" />
'
Select * from [dbo].[udf-XML-Hier](@XML1) Order By 1

Returns

The UDF if Interested

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

Declare @XML xml='<person><firstname preferred="Annie" nickname="BeBe">Annabelle</firstname><lastname>Smith</lastname></person>'
Select * from [dbo].[udf-XML-Hier](@XML) Order by R1
*/

Answer 2

This is - for sure! - the wrong approach. There is no I-read-any-XML-no-matter-what-structure-it-may-have-solution.

Look at the following, which comes close, but look at the third example especially carefully:

DECLARE @dummy TABLE(ID INT IDENTITY,Comment VARCHAR(100),YourXML XML);
INSERT INTO @dummy VALUES
 ('Your sample 1'
  ,N'<sender id="1" operation="NewValue" LastName="XYZ" loginId="10029" />
     <sender id="2" operation="OldValue" LastName="PQR" loginId="10029" />')
,('Your sample 2'
  ,N'<row> 
 <operation>NewValue</operation> 
 <LastName>Gandhi</LastName> 
 <loginId>1011</loginId> 
 </row> 
 <row> 
 <operation>OldValue</operation> 
 <LastName>Gandhi</LastName> 
 <loginId>1010</loginId> 
 </row>')
 ,('Show the limits'
  ,N'<row> 
     <element name="test">all have</element>
     <element name="other">the local-name()</element>
     <element name="One more">element</element>
     </row>');

 SELECT ID
       ,Comment
       ,'Element' AS ElementType
       ,t.x.value('local-name(..)','nvarchar(max)') AS ElementName
       ,t.x.value('.','nvarchar(max)') AS ElementValue                                
 FROM @dummy AS d
 CROSS APPLY d.YourXML.nodes(N'//*/text()') AS t(x)
 UNION ALL
 SELECT ID
       ,Comment
       ,'Attribute'
       ,t.a.value('local-name(.)','nvarchar(max)') AS ElementName
       ,t.a.value('.','nvarchar(max)') AS ElementValue                                
 FROM @dummy AS d
 CROSS APPLY d.YourXML.nodes(N'//*/@*') AS t(a)