XML : Dynamic columns and records from openxml in sql

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!

No comments:

Post a Comment