How to shred a XML being passed into a stored procedure?

Question

I have an XML being passed into a stored procedure in the following format. I'm struggling with how to shred it in SQL Server 2017:

declare @xml xml =  convert(xml, N'<SearchQuery>
    <DealTypeDesc>Deal</DealTypeDesc>
    <VendorNum>1</VendorNum>
    <VendorName>Vendor1</VendorName>
    <VendorNum>2</VendorNum>
    <VendorName>Vendor2</VendorName>
    <VendorNum>3</VendorNum>
    <VendorName>Vendor3</VendorName>
    <VendorNum>4</VendorNum>
    <VendorName>Vendor4</VendorName>
    <VendorNum>5</VendorNum>
    <VendorName>Vendor5</VendorName>
</SearchQuery>')

-- this is how it is being consumed now.  1 element at a time
SELECT 
    t.c.value('text()[1]', 'NVARCHAR(MAX)') AS LookupValue 
FROM 
    @xml.nodes('//SearchQuery/VendorNum') AS t(c)

-- I want the results to look like this where I can use the node name as a column and the value as a lookup.  
-- I wanted to do this in one pass without having to union all and select from the XML variable every time.
-- I can't change the XML structure since it's coming from a third party, so that option is out.
SELECT 
    'VendorName' ColumnName, 
    t.c.value('text()[1]', 'NVARCHAR(MAX)') AS LookupValue 
FROM 
    @xml.nodes('//SearchQuery/VendorName') AS t(c)

UNION ALL

SELECT 
    'VendorNum' ColumnName, 
    t.c.value('text()[1]', 'NVARCHAR(MAX)') AS LookupValue 
FROM 
    @xml.nodes('//SearchQuery/VendorNum') AS t(c)

Answer 1

You could query the two columns separately then join as follows:

select VendorNum, VendorName
from
(
    SELECT 
        n = ROW_NUMBER() OVER (Order by t.vendor.value('text()[1]','NVARCHAR(MAX)')),
        VendorNum= t.vendor.value('text()[1]','NVARCHAR(MAX)')
    FROM @xml.nodes('SearchQuery/VendorNum') AS t(vendor)
) N
join
(
    SELECT 
        n = ROW_NUMBER() OVER (Order by t.vendor.value('text()[1]','NVARCHAR(MAX)')),
        VendorName = t.vendor.value('text()[1]','NVARCHAR(MAX)') 
    FROM @xml.nodes('SearchQuery/VendorName') AS t(vendor)
) V on V.n =N.n

Answer 2

Two options here. The first is a simple query. The second is a helper function which will shred virtually any XML without having to know anything about it. I'll often use this during the Discovery Phase.

First Option

Select ColumnName  = a.value('local-name(.)','varchar(100)')
      ,LookupValue = a.value('.','varchar(max)') 
 From  @XML.nodes('SearchQuery')  as C1(n)
 Cross Apply C1.n.nodes('*') as C2(a)
 Where a.value('local-name(.)','varchar(100)') like 'Vendor%'

Returns

ColumnName  LookupValue
VendorNum   1
VendorName  Vendor1
VendorNum   2
VendorName  Vendor2
VendorNum   3
VendorName  Vendor3
VendorNum   4
VendorName  Vendor4
VendorNum   5
VendorName  Vendor5

The Second Option

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

Returns

Perhaps more than you want, but it is easy to trim down

The TVF if Interested

CREATE FUNCTION [dbo].[tvf-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].[tvf-XML-Hier](@XML) Order by R1
*/