Querying XML

Question

Post reply

Querying XML

tan110

Hall of Fame

Points: 3855
More actions
February 19, 2010 at 7:48 am

#103903

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,

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

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 SSC Guru Points: 260824 More actions · Answer 2

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 Hall of Fame Points: 3855 More actions · Answer 3

tan110

Hall of Fame

Points: 3855

February 19, 2010 at 9:23 am

#1121935

Thank you very much!!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

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 SSC Guru Points: 107049 More actions · Answer 6

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 7

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
SQLkiwi blog
@SQL_Kiwi

tan110 Hall of Fame Points: 3855 More actions · Answer 8

tan110

Hall of Fame

Points: 3855

February 21, 2010 at 6:48 am

#1122493

Again, thank you again everyone.

GSquared SSC Guru Points: 260824 More actions · Answer 9

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

Paul White SSC Guru Points: 150467 More actions · Answer 10

GSquared (2/22/2010)
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.

Thanks! The /text() is quite important too. I think it produces a very pleasing query plan overall, considering it is untyped XML.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

GSquared (2/22/2010)
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.

Actually, the performance difference does not seem to have anything to do with the CROSS APPLY, but rather entirely with the XML methods usage and the XQuery constructions. When I change Gus's query, to use Paul's XQueries, it produces the same plan (except, oddly, for the order of the X joins):

SELECT

R.Node.value('(CustomerId/text())[1]','varchar(100)') AS CustomerID,

R.Node.value('(CompanyName/text())[1]','varchar(100)') AS CompanyName

FROM @XML.nodes('/ROOT/Customers') R(Node);

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Oops, sorry, I missed your additional post, Paul...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 13

The text piece makes sense. My XQuery education has all been "trial and error", based on abysmal documentation and samples online and in BOL, so no great surprise that I missed that one.

- 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

Paul White SSC Guru Points: 150467 More actions · Answer 14

RBarryYoung (2/22/2010)
Oops, sorry, I missed your additional post, Paul...

That's 😎

I have a habit of using APPLY to neaten up my SELECT statements - I just like the style.

Plus, you get +1 cool dude points for every APPLY you write, so that's all good too.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi