SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Querying XML


Querying XML

Author
Message
tan110
tan110
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 712
Hi everyone,

I have the following table:

CREATE TABLE [dbo].[XmlImportTest](
[xml_data] [xml] NULL
)

-- I add the following data in:
insert into dbo.XmlImportTest (xml_data)
values
('<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>')

I want the result to come out like this:

Customerid CompanyName
1111 Sean Chai
1112 Tom Johnston
1113 Institue of Art

I've looked at some OPENXML & FOR XML examples, but still can't get a grasp on how to write this query. Please help.

Thanks,
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13232 Visits: 4894
I believe that you want the FOR XML EXPLICIT.

Please check the following examples:

http://www.sqlservercentral.com/articles/ADO/article4/516/

http://articles.sitepoint.com/article/data-as-xml-sql-server

Please let me know how you make out.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30197 Visits: 9730
DECLARE @XML XML;

SELECT @XML =
'<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';

SELECT --R.Node.query('.'),
R.Node.query('.').value('(/Customers/CustomerId/.)[1]','varchar(100)') AS CustomerID,
R.Node.query('.').value('(/Customers/CompanyName/.)[1]','varchar(100)') AS CompanyName
FROM @XML.nodes('/ROOT/Customers') R(Node);



You'll need to use your column name instead of the XML variable, but it should do what you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
tan110
tan110
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 712
Thank you very much!!!
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19074 Visits: 9518
Welsh Corgi (2/19/2010)
I believe that you want the FOR XML EXPLICIT.

Welsh: Just an FYI, but "FOR XML EXPLICIT" should always be a last resort for XML construction, it is a truly brutal facility. Most things that you need can almost always be constructed with "FOR XML PATH", which is about 100x easier to use. And a little string manipulation can usually get the rest.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13232 Visits: 4894
Mr Young,

Thank you for setting me straight. :-)

I appreciate & respect your input.

If you think of any instructional articles and if you get an chance I would appreciate if you would share them with me.

Regards,
WC

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
LutzM
LutzM
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12819 Visits: 13559
The "XML Workshop" series of articles by Jacob Sebastian (search this site for details, please) is a great start and covers the import of xml data as well as formatting relational data in xml format.

One link as a start:
http://www.sqlservercentral.com/articles/Miscellaneous/2996/



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20058 Visits: 11359
GSquared (2/19/2010)
You'll need to use your column name instead of the XML variable, but it should do what you want.

A much more efficient query plan is produced from this code:


DECLARE @XML XML;

SET @XML =
N'
<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';

SELECT DV.customer_id,
DV.company_name
FROM @XML.nodes('./ROOT/Customers')
AS T(customers)
CROSS
APPLY (
SELECT customers.value('(CustomerId/text())[1]', 'VARCHAR(100)'),
customers.value('(CompanyName/text())[1]', 'VARCHAR(100)')
)
AS DV (customer_id, company_name);



Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
tan110
tan110
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 712
Again, thank you again everyone.
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30197 Visits: 9730
Paul White (2/21/2010)
GSquared (2/19/2010)
You'll need to use your column name instead of the XML variable, but it should do what you want.

A much more efficient query plan is produced from this code:


DECLARE @XML XML;

SET @XML =
N'
<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>';

SELECT DV.customer_id,
DV.company_name
FROM @XML.nodes('./ROOT/Customers')
AS T(customers)
CROSS
APPLY (
SELECT customers.value('(CustomerId/text())[1]', 'VARCHAR(100)'),
customers.value('(CompanyName/text())[1]', 'VARCHAR(100)')
)
AS DV (customer_id, company_name);



Paul


Clever. I hadn't thought of using Cross Apply at that point in the query. (Use it all the time in separating out the nodes. Just never have for the values.) I'll have to do some speed tests with that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search