Get value from specific xml name value pair using sql

Question

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

Rick · Accepted Answer · 2013-04-10 02:13:41Z

1

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

answered Apr 10, 2013 at 2:13

Rick

1,0738 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get value from specific xml name value pair using sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related