Query XML creating field names whithout knowing no

Question

If I have a SQL SERVER 2012 table containing an XML field type. The records it could contain are as follows.

I have simplified my problem to the following.

Record 1:

ID_FIELD='nn1'

XML_FIELD=
<KNOWN_NAME_1>
    <UNKNOWN_NAME1>Some value</UNKNOWN_NAME1>
    <UNKNOWN_NAME2>Some value</UNKNOWN_NAME2>
  ... Maybe more ...
</KNOWN_NAME_1>

Record 2:

ID_FIELD='nn2'

XML_FIELD=
<KNOWN_NAME_2>
  <UNKNOWN_NAME1>Some value</UNKNOWN_NAME1>
  <UNKNOWN_NAME2>Some value</UNKNOWN_NAME2>
  ... Maybe more unknown fields ...
</KNOWN_NAME_2>

I want to output non xml:

UNKNOWN_NAME1 | UNKNOWN_NAME2 | ETC
-----------------------------------
Some Value      Some value

For a known root value (i.e. KNOWN_NAME_1)

I.e. If I new the node values (which I don't) I could

SELECT
  XMLData.Node.value('UNKNOWN_NAME1[1]', 'varchar(100)') ,
  XMLData.Node.value('UNKNOWN_NAME2[1],  'varchar(100)')
FROM FooTable
  CROSS APPLY MyXmlField.nodes('//KNOWN_NAME_1') XMLData(Node)
  -- WHERE SOME ID value = 'NN1' (all XML records have a separate id)

All is good however I want to do this for all the nodes (unknown quantity) without knowing the node names. The root will only contain nodes it wont get any deeper.

Is this possible in SQL?

I have looked at this but I doubt I can get enough rights to implement it.

http://architectshack.com/ClrXmlShredder.ashx

Answer 1

If you don't know the column names in the output you have to use dynamic SQL:

-- Source table
declare @FooTable table
(
  ID_FIELD char(3),
  XML_FIELD xml
)

-- Sample data
insert into @FooTable values
('nn1', '<KNOWN_NAME_1>
           <UNKNOWN_NAME1>Some value1</UNKNOWN_NAME1>
           <UNKNOWN_NAME2>Some value2</UNKNOWN_NAME2>
         </KNOWN_NAME_1>')

-- ID to look for
declare @ID char(3) = 'nn1'

-- Element name to look for
declare @KnownName varchar(100) = 'KNOWN_NAME_1'

-- Variable to hold the XML to process
declare @XML xml

-- Get the XML
select @XML = XML_FIELD
from @FooTable
where ID_FIELD = @ID

-- Variable for dynamic SQL
declare @SQL nvarchar(max)

-- Build the query
select @SQL = 'select '+stuff(
  (
  select ',T.N.value('''+T.N.value('local-name(.)', 'sysname')+'[1]'', ''varchar(max)'') as '+T.N.value('local-name(.)', 'sysname')
  from @XML.nodes('/*[local-name(.)=sql:variable("@KnownName")]/*') as T(N)
  for xml path(''), type
  ).value('.', 'nvarchar(max)'), 1, 1, '')+
  ' from @XML.nodes(''/*[local-name(.)=sql:variable("@KnownName")]'') as T(N)'

-- Execute the query
exec sp_executesql @SQL, 
     N'@XML xml, @KnownName varchar(100)', 
     @XML = @XML, 
     @KnownName = @KnownName

Result:

UNKNOWN_NAME1   UNKNOWN_NAME2
--------------- ---------------
Some value1     Some value2

The dynamically generated query looks like this:

select T.N.value('UNKNOWN_NAME1[1]', 'varchar(max)') as UNKNOWN_NAME1,
       T.N.value('UNKNOWN_NAME2[1]', 'varchar(max)') as UNKNOWN_NAME2 
from @XML.nodes('/*[local-name(.)=sql:variable("@KnownName")]') as T(N)

SE-Data