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


Querying XML


Querying XML

Author
Message
tan110
tan110
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 720
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
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46042 Visits: 4895
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 Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94663 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 720
Thank you very much!!!
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55194 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
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46042 Visits: 4895
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
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38151 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 Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54674 Visits: 11391
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1476 Visits: 720
Again, thank you again everyone.
GSquared
GSquared
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94663 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