Hi I have the following table:
CREATE TABLE XmlTable ([ImportData] [xml] NOT NULL) ON [Primary]
I have the following XML format:
and I want to select all the child values like this:
SELECT child.value('(parent::node()/ID/text)', 'NVARCHAR(50)') AS [ID]
, child.value('(State/text)', 'NVARCHAR(50)') AS [State]
, child.value('(Value/text)', '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 :-(
-- Stop your grinnin' and drop your linen...they're everywhere!!!