Dynamic columns and records from openxml in sql

Question

I have an xml document that's pretty straightfoward. The only difference is that the elements can change. One time I might have:

<data><PersonalInfo>
<Person><FirstName>Bob</FirstName><LastName>Smith</LastName></Person>
<Person><FirstName>John</FirstName><LastName>Doe</LastName></Person>
</PersonalInfo></data>

The next time I might have:

<data><AddressInfo>
<Address><City>Cleveland</City><State>OH</State></Address>
<Address><City>Chicago</City><State>IL</State></Address>
</AddressInfo></data>

I would like to write a select statement that produces a dynamic table depending on which xml doc I've got at the moment.

Ex: For the first one:

First Name     Last Name
------------------------
Bob             Smith
John            Doe
Etc...

For the 2nd one

City       State
-----------------------
Cleveland   OH
Chicago     IL
Etc...

The 2 examples are not related in any way (Bob is not from Cleveland, etc...)

I just want to use the same code to produce both tables...depending on the xml doc. The only difference will be the node reference, of course:

Example 1:  data/PersonalInfo/Person*
Example 2:  data/AddressInfo/Address*

I do not want to combine or change anything in the xml doc structure. They are what they are coming in. How can I reference each one to create the two different tables above - each xml doc coming in will be in a separate run stored procedure. But it will be the same stored procedure. Any assistance is greatly appreciated, thanks in advance!

Answer 1

Try it like this:

CREATE PROCEDURE dbo.TransformPlainXML(@InputXml XML)
AS
BEGIN

DECLARE @PivotColumns NVARCHAR(MAX);

WITH DistinctElementNames AS
(
    SELECT DISTINCT '[' + Element.value('fn:local-name(.)','varchar(max)') + ']' AS ElementName
    FROM @InputXml.nodes('/data/*/*/*') As One(Element)
)
SELECT @PivotColumns = STUFF(
(
    SELECT ',' + ElementName
    FROM DistinctElementNames
    FOR XML PATH('')
),1,1,'');

DECLARE @cmd NVARCHAR(MAX)=
'WITH Lines AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowIndex
          ,Line.query(''.'') AS OneLine
    FROM @xml.nodes(''/data/*/*'') As One(Line)
)
SELECT p.*
FROM
(
    SELECT RowIndex
          ,Element.value(''.[1]'',''varchar(max)'') AS ElementValue
          ,Element.value(''fn:local-name(.)'',''varchar(max)'') AS ElementName
    FROM Lines
    CROSS APPLY OneLine.nodes(''./*/*'') AS The(Element)
) AS tbl
PIVOT
(
    MIN(ElementValue) FOR ElementName IN(' + @PivotColumns + ')
) AS p
';

EXECUTE sp_executesql @cmd,N'@xml XML',@xml=@InputXml;
END
GO

And test it like this

declare @xml1 XML=
'<data>
  <PersonalInfo>
    <Person>
      <FirstName>Bob</FirstName>
      <LastName>Smith</LastName>
    </Person>
    <Person>
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
    </Person>
  </PersonalInfo>
</data>';


EXEC TransformPlainXML @xml1;

declare @xml2 XML=
'<data>
  <AddressInfo>
    <Address>
      <City>Cleveland</City>
      <State>OH</State>
    </Address>
    <Address>
      <City>Chicago</City>
      <State>IL</State>
    </Address>
  </AddressInfo>
</data>';
EXEC TransformPlainXML @xml2;

The two results:

RowIndex    FirstName   LastName
1           Bob         Smith
2           John        Doe

And

RowIndex    City        State
1           Cleveland   OH
2           Chicago     IL