Tutorial :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 :)


Solution:1

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.....


Solution:2

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »