I have xml column with a nested xml data.
I can get the ID using the following query but cant get past the rOut tag. Can somebody point me in the right direction?
SELECT XmlData.value('(/Msg/sMain/ID)[1]' , 'int') as ID
FROM myTable
Below is my xml data format and required output
<Msg>
<sMain>
<ID>1234</ID>
</sMain>
<rOut xmlns:xsi="http://www.w6.org/xe/XMLdata-load"
xmlns:xsd="http://www.foobar.org/my/schema"
xmlns="http://www.w3.org/2001/results">
<Header>
<Flag>Y</Flag>
</Header>
<Body>
<RequestedBy>John Doe</RequestedBy>
<Feed>
<Xaxis>
<val1>120</Val1>
<BigValues>
<Val2>230</Val2>
<Val3>250</Val3>
</BigValues>
<SmallValus>158</SmallValues>
</Xaxis>
</Feed>
</Body>
</rOut>
</Msg>
ID Flag Requestedby Val1 Val2 Val3 SmallValues
1234 Y John Doe 120 230 250 158
Your sample xml has few typos, for SmallValues & Val1..try the following:
DECLARE @TBL TABLE(XmlData XML)
INSERT INTO @TBL (XmlData) VALUES(@xmlData)
;WITH XMLNAMESPACES ('http://www.w3.org/2001/results' AS xml_ns)
select COL.value('(sMain/ID/text())[1]', 'INT') AS ID,
COL.value('(xml_ns:rOut/xml_ns:Header/xml_ns:Flag/text())[1]', 'VARCHAR(1)') AS Flag ,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:RequestedBy/text())[1]', 'VARCHAR(100)') AS RequestedBy,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:Val1/text())[1]', 'INT') AS Val1,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:BigValues/xml_ns:Val2/text())[1]', 'INT') AS Val2,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:BigValues/xml_ns:Val3/text())[1]', 'INT') AS Val3,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:SmallValues/text())[1]', 'INT') AS SmallValues
FROM @TBL TBL
CROSS APPLY TBL.XmlData.nodes('/Msg')XML_TAB(Col)
/*
IF YOU HAVE A VARIABLE:
;WITH XMLNAMESPACES ('http://www.w3.org/2001/results' AS xml_ns)
SELECT COL.value('(sMain/ID/text())[1]', 'INT') AS ID,
COL.value('(xml_ns:rOut/xml_ns:Header/xml_ns:Flag/text())[1]', 'VARCHAR(1)') AS Flag ,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:RequestedBy/text())[1]', 'VARCHAR(100)') AS RequestedBy,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:Val1/text())[1]', 'INT') AS Val1,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:BigValues/xml_ns:Val2/text())[1]', 'INT') AS Val2,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:BigValues/xml_ns:Val3/text())[1]', 'INT') AS Val2,
COL.value('(xml_ns:rOut/xml_ns:Body/xml_ns:Feed/xml_ns:Xaxis/xml_ns:SmallValues/text())[1]', 'INT') AS SmallValues
FROM @xmlData.nodes('/Msg') TAB(COL)
*/
Thank you @rocknpop
Any reason why you used the 3rd URL on the following line ?
WITH XMLNAMESPACES ('http://www.w3.org/2001/results' AS xml_ns)
Also, is there a way to reference the attribute I want without listing the all the sub nodes ? Wondering if I can use something like /*/
You have namespace in the xml so either we use that or there is a shorthand that you can try, instead of xml_ns use a *. So instead of "xml_ns:" use can use "*:", then you don't need to specify -> with xmlnamespaces...
OK, thank you. Much appreciated. I owe you beer.
Will give it a spin and sync back.
use sqlteam
go
declare @xml xml =
'<Msg>
<sMain>
<ID>1234</ID>
</sMain>
<rOut xmlns:xsi="http://www.w6.org/xe/XMLdata-load"
xmlns:xsd="http://www.foobar.org/my/schema"
xmlns="http://www.w3.org/2001/results">
<Header>
<Flag>Y</Flag>
</Header>
<Body>
<RequestedBy>John Doe</RequestedBy>
<Feed>
<Xaxis>
<Val1>120</Val1>
<BigValues>
<Val2>230</Val2>
<Val3>250</Val3>
</BigValues>
<SmallValues>158</SmallValues>
</Xaxis>
</Feed>
</Body>
</rOut>
</Msg>'
select @xml.value('(//*[local-name()="ID"])[1]', 'int') ID,
@xml.value('(//*[local-name()="Flag"])[1]', 'nvarchar(max)') Flag,
@xml.value('(//*[local-name()="RequestedBy"])[1]', 'nvarchar(max)') as RequestedBy,
@xml.value('(//*[local-name()="Val1"])[1]', 'nvarchar(max)') as Val1,
@xml.value('(//*[local-name()="Val2"])[1]', 'nvarchar(max)') as Val2,
@xml.value('(//*[local-name()="Val3"])[1]', 'nvarchar(max)') as Val3,
@xml.value('(//*[local-name()="SmallValues"])[1]', 'nvarchar(max)') as SmallValues
Awesome ! Thanks.
Follow up question.
For repeating groups/tags. I see that the value of index 2 say val[1] is assigned to val[2] if index 1 has missing values. Any thoughts on how to fix it. Looks like val[1] is assigned the value of the fist non missing item within the repeating chain. How do I preserve the order?
what? I don't understand what you are saying ucal? What are groups/tags? What is index? Please use existing xml to identify what you are asking?
Solution
use sqlteam
go
declare @xml xml =
'<Msg>
<sMain>
<ID>1234</ID>
</sMain>
<rOut xmlns:xsi="http://www.w6.org/xe/XMLdata-load"
xmlns:xsd="http://www.foobar.org/my/schema"
xmlns="http://www.w3.org/2001/results">
<Header>
<Flag>Y</Flag>
</Header>
<Body>
<RequestedBy>John Doe</RequestedBy>
<Feed>
<Xaxis>
<Val1>120</Val1>
<BigValues>
<Val2>230</Val2>
<Val3>250</Val3>
</BigValues>
<BigValues>
<Val2>230</Val2>
<Val3>250</Val3>
<Val3>260</Val3>
</BigValues>
<SmallValues>158</SmallValues>
</Xaxis>
</Feed>
</Body>
</rOut>
</Msg>'
Sorry for not making my post clear. Consider the updated XML above with added Val3 under the BigValues tag. The first group has one Val3 and second has 2 levels of Val3. How do I specify the index for correct assignment. The second val3 is assigned to the first group for the [2] index.
So it is not static xml but can keep changing like that dynamically?
Yes, correct, it can change.
In that case maybe this function called XMLTable might be of great help. then call it using and see what you can do such as filtering etc.
SELECT NodeName, Value , *
FROM dbo.XMLTable(@xml)
where NodeName like '%Val%'