Get value from specific xml name value pair using sql

Discussion in 'XML Forums' started by MansWisdom, Apr 10, 2013.

  1. MansWisdom

    MansWisdom New Member

    I am writing a query in SQL to combine a few tables of data and have got to one where it is stored in XML in the following format:\[code\]<CustomDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Fields> <Field> <Name>Selected City</Name> <Value>Central</Value> </Field> <Field> <Name>Address Provided</Name> <Value>New Address</Value> </Field> </Fields></CustomDetails>\[/code\]The XML is stored in a table and I have managed to get the \[code\]Address Provided\[/code\] field using the following code\[code\]select o_OrderID, od.CustomDetails.query('data(/CustomDetails/Fields/Field[2]/Value)') as 'Address Provided'from dbo.[Order] o on o_OrderID = s.OrderID join dbo_OrderData od on od.OrderID = o_OrderID\[/code\]I would like to know if there is a better way of doing this and also if there is a way to guarantee that I will get the Address Provided field even if it appears first or if there are other fields in front of it.Hopefully that all makes sense, Any help is appreciated.Thanks
     

Share This Page