Query XML creating field names whithout knowing node names
===

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

