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

[bill]

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>

[Erland Sommarskog]

Post by bill
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 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

So, if you want to know just enough XQuery to be dangegrous, look at
http://www.sommarskog.se/arrays-in-sql-2005.html#XML. One thing you will
learn is that attribute-centred XML is a lot easier to deal with.

And here is a query for you:


SELECT T.c.value('(vin_tx/text())[1]', 'varchar(17)'),
T.c.value('(manufacturer_nm/text())[1]', 'varchar(20)'),
T.c.value('(model_year_nr/text())[1]', 'varchar(40)'),
T.c.value('(model_nm/text())[1]', 'varchar(40)')
FROM @xml_data.nodes('/CarInfo')AS T(c)

[bill]

I forgot to thank you Erland. You're always helpful, and really know
your stuff.

Thanks,

Bill