Challenge - Processing XML into T-SQL table structure?

Question

If you have this XML:

<people>
  <person id="1">
    <firstname>John</firstname>
    <surname>Doe</surname>
  </person>
  <person id="2">
    <firstname>Mary</firstname>
    <surname>Jane</surname>
  </person>
</people>

And you wanted this table:

id  firstname  surname
--- ---------- ----------
1   John       Doe
2   Mary       Jane

How would you get it there, using T-SQLXML?

And to throw a spanner into the mix: Lets say you know the depth of the <person>, <firstname> and <surname> elements, but you don't know what they're called!

Feel free to flame if you think this is better posted to reddit :)

Answer 1

This will get you the table. If you don't know the XML column names, then you would probably have to use dynamic SQL (e.g. for "firstname varchar(20) 'firstname'", you'd have to replace 'firstname' with whatever the XML column name is, which I assume you would determine at runtime):

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<people>
  <person id="1">
    <firstname>John</firstname>
    <surname>Doe</surname>
  </person>
  <person id="2">
    <firstname>Mary</firstname>
    <surname>Jane</surname>
  </person>
</people>
'
/* Create an internal representation of the XML document */
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/people/person',1)
            WITH (id varchar(20),
                  firstname varchar(20) 'firstname',
                  surname varchar(20) 'surname')
EXECUTE sp_xml_removedocument @idoc

Result:

id  firstname       surname
1   John            Doe
2   Mary            Jane

Answer 2

I would recommend using the XQuery interface, rather than the rather clunky old OPENXML approach:

SELECT
    Ppl.Person.value('(@id)[1]', 'int') AS 'ID',
    Ppl.Person.value('(firstname)[1]', 'varchar(20)') AS 'First Name',
    Ppl.Person.value('(surname)[1]', 'varchar(20)') AS 'Last Name'
FROM
    @input.nodes('/people/person') as Ppl(Person)

This is the preferred way of doing it in SQL Server 2005 and forward.

The output is the same:

ID  First Name  Last Name
1    John             Doe
2    Mary             Jane

You can't really do this however, if you don't know the XML structure.....