Get value from specific xml name value pair using sql

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
 
Top