Querying XML Column

Question

Post reply

Querying XML Column

Mark Johnson-176476

SSCommitted

Points: 1603
More actions
August 31, 2009 at 4:02 pm

#137709

I have a table with and XML column similar to the example below:
CREATE TABLE Test1 (ItemID int, SerialNumbers XML)
INSERT INTO Test1 (ItemID, SerialNumbers) VALUES (1,
'
123456
654321
123654
321456
')
INSERT INTO Test1 (ItemID, SerialNumbers) VALUES (2,
'
456789
987654
456987
456987
')
I am trying to come up with a query that will return the ItemID for each record where a specified serial number exists in the XML.
Is there a way to query the table and return the ItemID if the specified SN exists in the XML column?
Thanks for any help you can provide.

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Is that really how the XML data is stored?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

John Rowan SSC Guru Points: 56440 More actions · Answer 2

If so, you can't query the XML because there are no nodes defined. You'll need to convert it to a varchar and use standard character handling functions.

SELECT *

FROM #Test1

WHERE CHARINDEX('456789',CONVERT(varchar(max),SerialNumbers)) > 0

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Mark: Please edit your post and add [ code ] tags to make your SQL and XML code correctly visible.

[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]

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Duh, I didn't even think that the forum might have pulled the tags out!!

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 5

My appologies for the missing tags. Below is the code.

I have a table with and XML column similar to the example below:

CREATE TABLE Test1 (ItemID int, SerialNumbers XML);

GO

INSERT INTO Test1 (ItemID, SerialNumbers) VALUES (1,

'

123456

654321

123654

321456

');

INSERT INTO Test1 (ItemID, SerialNumbers) VALUES (2,

'

456789

987654

456987

');

I am trying to come up with a query that will return the ItemID for each record where a specified serial number exists in the XML.

Is there a way to query the table and return the ItemID if the specified SN exists in the XML column?

Thanks for any help you can provide.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

thanks, Mark.

[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]

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

select itemid,

tbl.col.value('(.)[1]','int')

from Test1 cross apply SerialNumbers.nodes ('/SERIALNUMBERS/SERIALNUMBER') tbl (col)

If what you need is to get the ItemID of a known serial number, you can use the exists method:

select itemid

from Test1

where SerialNumbers.exist('/SERIALNUMBERS/SERIALNUMBER/text()[.="123654"]')=1

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 8

Thanks! That works great.

I really appreciate the help.

Mark