Filtering results from SELECT on XML column

Question

Filtering results from SELECT on XML column

Gary Varga

SSC Guru

Points: 82166
More actions
April 5, 2012 at 6:47 am

#100469

Hi I have the following table:
CREATE TABLE XmlTable ([ImportData] [xml] NOT NULL) ON [Primary]
I have the following XML format:
<Parent>
<ID>1234</ID>
<Child>
<State>1</State>
<Value>X</Value>
</Child>
<Child>
<State>2</State>
<Value>Y</Value>
</Child>
</Parent>
and I want to select all the child values like this:
SELECT child.value('(parent::node()/ID/text)[1]', 'NVARCHAR(50)') AS [ID]
, child.value('(State/text)[1]', 'NVARCHAR(50)') AS [State]
, child.value('(Value/text)[1]', 'NVARCHAR(50)') AS [Value]
FROM [XmlTable] AS T CROSS APPLY [ImportData].nodes('/Parent/Child') AS ImportData(child)
I need to restrict (like a WHERE clause) on the value of the State element of the Child element e.g. all States where it isn't 1:
ID State Value
1234 2 Y
I have tried various methods for half a day now. Can anyone help? Please?
PS Please excuse any typos as I am working in a Hyper-V VM with wireless network access so it has all been retyped by hand as I cannot access network from VM 🙁
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Pretty sure you want to use the .exist() method. Check out his blog post about it: http://www.jasonstrate.com/2010/11/xquery-for-the-non-expert-exist/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gary Varga SSC Guru Points: 82166 More actions · Answer 2

Jack Corbett (4/5/2012)
Pretty sure you want to use the .exist() method. Check out his blog post about it: http://www.jasonstrate.com/2010/11/xquery-for-the-non-expert-exist/

Thanks Jack (sorry for being tardy with this overdue gratitude!!!)

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

Also this

SELECT child.value('(parent::node()/ID/text())[1]', 'NVARCHAR(50)') AS [ID]

, child.value('(State/text())[1]', 'NVARCHAR(50)') AS [State]

, child.value('(Value/text())[1]', 'NVARCHAR(50)') AS [Value]

FROM [XmlTable] AS T

CROSS APPLY [ImportData].nodes('/Parent/Child[State/text() != "1"]') AS ImportData(child)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537