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 
rderID, od.CustomDetails.query('data(/CustomDetails/Fields/Field[2]/Value)') as 'Address Provided'from dbo.[Order] o on rderID = s.OrderID join dbrderData od on od.OrderID = rderID\[/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
rderID, od.CustomDetails.query('data(/CustomDetails/Fields/Field[2]/Value)') as 'Address Provided'from dbo.[Order] o on rderID = s.OrderID join dbrderData od on od.OrderID = rderID\[/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