Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtering results from SELECT on XML column Expand / Collapse
Author
Message
Posted Thursday, April 05, 2012 6:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1278749
Posted Thursday, April 05, 2012 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 10,907, Visits: 12,540
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1278823
Posted Tuesday, April 16, 2013 2:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1442641
Posted Tuesday, April 16, 2013 2:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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)



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1442650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse