Create Columns from a XML Data Type column with an undefined number of elements in SQL Server 2008

Question

I have a column of XML data type with the following contents:

<Station>
  <row>
    <lid>1055</lid>
    <sname>some name</sname>
    <sipadress>111.222.011.112</sipadress>
    <sdescription>Some description</sdescription>

    ... many more elements ...

  </row>
</Station>

I'd like to have a view or a result which looks something like that:

lid |sname    |sipadress      |sdescription    | ...
-----------------------------------------------------
1055|some name|111.222.011.112|Some description| ...

I've searched for hours and tried sp_xml_preparedocument, CROSS APPLY... but for every solution, you have to specify the elements, respectively the column-names (lid, sname, sipadress...)

I'm searching for a way to get all the descendants of <row>, wherever the number, and without specifying the name of them and then to create columns for them.

Thanks in advance!

Use a combination of the query provided by @marc_s and the dynamic pivot in the dupe. If the columns you want is unknown before execution you have to build the query dynamically. — Mikael Eriksson, Jan 09 '15 at 07:03

score 2 · Answer 1 · answered Jan 08 '15 at 16:39

How about this:

DECLARE @input TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @input VALUES(1, '<Station>
  <row>
    <lid>1055</lid>
    <sname>some name</sname>
    <sipadress>111.222.011.112</sipadress>
    <sdescription>Some description</sdescription>
    <anything>test</anything>
    <anything2>test</anything2>
    <anything3>test</anything3>
  </row>
</Station>'), (2, '<Station>
  <row>
    <lid>1055</lid>
    <sname>some name</sname>
    <sipadress>111.222.011.112</sipadress>
    <sdescription>Some description</sdescription>
    <something>test</something>
    <something2>test</something2>
    <something3>test</something3>
  </row>
</Station>')

SELECT
    ID,
    ElementName = XC.value('local-name(.)', 'varchar(50)'),
    ElementValue = XC.value('(.)[1]', 'varchar(250)')
FROM
    @input
CROSS APPLY
    XmlContent.nodes('/Station/row/*') AS XT(XC)

I get this output from it:

enter image description here

The XQuery expression /Station/row/* just selects all elements directly under each <row> node - no matter how many there are and what they're called.

Then I use the XQuery function local-name(.) to get the name of the XML element, and the (.) notation to get the value of the XML elment.

Mind you: this works for all strings - any other things (like <lid> which appears to be an int) will also be returned as a varchar(250) in this approach - but you do get all direct child nodes and their values.

Thanks. I had this before, yes. But isn't there a way to create columns out of the element names and fill them with the corresponding element values? Another way I thought of would be your answer and then somehow use the rows to create columns. — Olli Peh, Jan 09 '15 at 06:41

Create Columns from a XML Data Type column with an undefined number of elements in SQL Server 2008

1 Answers1