Tsql Query xml columns by nodes
-
I have created an parmeter @myxml and populated it with my xml. I am able to get most of the data returned but I am running in to a problem with a node called userarea. if i am select s.PO.value(':UserArea[1]/:Property[4]', 'nvarchar(50)') as MFG I can get the mfg name however if one of the propertys are not in the xml then then i get the wrong value. is there a way to call the property by its name and not the index.Property[Manufacture]??
<UserArea>
<Property>
<NameValue name="ActiveFlag">true</NameValue>
</Property>
<Property>
<NameValue name="ExchangeRate">1.00</NameValue>
</Property>
<Property>
<NameValue name="Manufacturer">FoxCon</NameValue>
</Property>
<Property>
<NameValue name="ManufacturerPart">456889</NameValue>
</Property>
<Property>
<NameValue name="TransactionNumber"/>
</Property>
<Property>
<NameValue name="TransactionLine"/>
</Property>
<Property>
<NameValue name="eam.UDFNUM02"/>
</Property>
</UserArea>
-
Try this
s.PO.value('(/UserArea/Property/NameValue[@name="ManufacturerPart"])[1]', 'nvarchar(50)') as MFG
____________________________________________________
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
-
Duplicate deleted
____________________________________________________
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 -
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply