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

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>




Bill_MacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pivarCommented:
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
0
pivarCommented:
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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill_MacAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.