We help IT Professionals succeed at work.

XML Round Trip:  Create XML Data Set from Table, Insert into New table. SQL 2008

Bill_Mac
Bill_Mac asked
on
I have asked this question on the usenet, but no answers yet.  

I want to :
1. Select from a table into an XML variable.
2. Select from the variable and get the exact same result set as if I had done a select from the original table.

I have googled a lot, but not found what I am looking for.  I freely admit that I know nothing about XML.  I don't want to do anything too fancy.  I just want to do items 1 and 2.  SSIS and backup/restore aren't going to work for my purposes.

I don't want  to learn a bunch of sfuff to create populate the xml variable.  I am hoping to just use FOR XML AUTO, ELEMENTS to create the xml.

Because XML is so foreign to me, I need the exact query to run against @xml_data below.  

I DO NOT want to query from a table, but from a varaible that is populated with xml.

Thanks,

Bill MacLean
(See below for sample table, etc.  I just need the query that returns the result set from the @xml_data variable)

Sample:


CREATE TABLE xml_test
(
  vin_tx VARCHAR(17)
 ,manufacturer_nm VARCHAR(30)
 ,model_year_nr INT
 ,model_nm VARCHAR(40)
 ,PRIMARY KEY CLUSTERED (vin_tx)
 )


INSERT INTO xml_test VALUES ('1234567890ABCDEF1', 'Kia', 2011,
'Sportage')
INSERT INTO xml_test VALUES ('1234567890ABC123', 'Kia', 2011,
'Sportage')
INSERT INTO xml_test VALUES ('ABC4567890ABC123', 'Ford', 2011,
'F-150')
INSERT INTO xml_test VALUES ('ABC4588890ABC123', 'Ford', 2007,
'F-150')
INSERT INTO xml_test VALUES ('CCC4588890ABC123', 'Ford', 2007,
'F-150''s test tricky data')


Run this query to assign the xml to a variable:


DECLARE @xml_data xml
SET @xml_data =(SELECT * FROM xml_test AS CarInfo FOR XML AUTO,
ELEMENTS)
SELECT @xml_data


I want a SELECT statement that will turn the XML back into a tabular
data set.  Call this desired query 'X'.  If you run query 'X', the
result set should be the same as SELECT * FROM xml_test


I appreciate any pointers (an actual query would be even better!).  I
have done quite a bit of googling, but all the articles I find are too
complex because they focus on master/detail relationships, etc.  I
just want a way to get an XML representation of the tablular data set
and be able to re-create it easily.


I am not interested in backup/restore, SSIS, etc.


Thanks,


Bill


PS: The value in the @xml_data variable looks like this:


<CarInfo>
  <vin_tx>1234567890ABC123</vin_tx>
  <manufacturer_nm>Kia</manufacturer_nm>
  <model_year_nr>2011</model_year_nr>
  <model_nm>Sportage</model_nm>
</CarInfo>
<CarInfo>
  <vin_tx>1234567890ABCDEF1</vin_tx>
  <manufacturer_nm>Kia</manufacturer_nm>
  <model_year_nr>2011</model_year_nr>
  <model_nm>Sportage</model_nm>
</CarInfo>
<CarInfo>
  <vin_tx>ABC4567890ABC123</vin_tx>
  <manufacturer_nm>Ford</manufacturer_nm>
  <model_year_nr>2011</model_year_nr>
  <model_nm>F-150</model_nm>
</CarInfo>
<CarInfo>
  <vin_tx>ABC4588890ABC123</vin_tx>
  <manufacturer_nm>Ford</manufacturer_nm>
  <model_year_nr>2007</model_year_nr>
  <model_nm>F-150</model_nm>
</CarInfo>
<CarInfo>
  <vin_tx>CCC4588890ABC123</vin_tx>
  <manufacturer_nm>Ford</manufacturer_nm>
  <model_year_nr>2007</model_year_nr>
  <model_nm>F-150's test tricky data</model_nm>
</CarInfo>




Comment
Watch Question

Commented:
Hi,

Have a look at the nodes function:

http://msdn.microsoft.com/en-us/library/ms188282(v=SQL.90).aspx

Unfortunately, I don't have access to a SQL server right now, perhaps I can give you a working example later if you still need it.

/peter
Commented:
OK, here is an example


select T.c.query('vin_tx/text()') AS vin_tx,
 T.c.query('manufacturer_nm/text()') AS manufacturer_nm,
 T.c.query('model_year_nr/text()') AS model_year_nr,
 T.c.query('model_nm/text()') AS model_nm
from @xml_data.nodes('/CarInfo') AS T(c)

Author

Commented:
Sorry it took so long to mark this question as solved.  I tried the solution long ago and it worked.  Thought I marked the question "solved" but apparently I did not.