CODEWITHSUNDEEP
sqlxml
Rick
Rick

Reputation: 1073

Get value from specific xml name value pair using sql

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:

<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>

The XML is stored in a table and I have managed to get the Address Provided field using the following 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

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

Upvotes: 0

Views: 880

Answers (1)

Rick
Rick

Reputation: 1073

After some research I managed to get a solution.

select
    o.OrderID,
    cast(od.CustomDetails.query('
       for $CD in /CustomDetails/Fields/Field,
           $Name in $CD/Name 
       where contains($Name, "Address Provided")
       return data($CD/Value)
    ') as varchar(50)) as addressProvided
from
    dbo.[Order] o on
        o.OrderID = s.OrderID
            join
                dbo.OrderData od on
                            od.OrderID = o.OrderID

Upvotes: 1

Related Questions