Create XML from table, then re-create tabular data set from XML: How to?

Giganews Newsgroups
Subject: Create XML from table, then re-create tabular data set from XML: How to?
Posted by:  bill (billmaclea…@gmail.com)
Date: Fri, 2 Dec 2011

I am a complete noob when it comes to XML.  Not to sound lazy, but I
kind of want  to remain a noob regarding XML :)

I only want to learn the absolute bare bones that will perform my
requirements.

My requirements:
1. Create an XML file of the data from an existing table in the
simplest way possible.
2. Write a query that will read the xml and "re-create" the tablular
data set.

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>

Replies