Extracting a value from a XML column in SQL Server 2012

Question

I have seen many questions addressing this issue, but unfortunately I still was not able to make it work.

Here's an example of the XML data contained in an XML column called RentalValueAmount in a table called Units:

<X C="1" I="0">
  <E D="1000Y0M0W0D" P="1" A="36500" />
</X>

I tried this but did not get any values:

select 
    cast(RentalValueAmount as XML).value('data(/X/E)[1]','varchar(10)') as test
from dbo.units

I need to extract or return 36500 as a number using a query but I have not been able to do so. Obviously I do not know XML, so I would really appreciate the help.

Code Different · Accepted Answer · 2015-02-25 01:43:56Z

1

Try this:

select 
cast(RentalValueAmount as XML).value('(/X/E)[1]/@A','varchar(10)') as test
from dbo.units

If the column is already of XML data type, you don't need to cast it to XML again.

answered Feb 25, 2015 at 1:43

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

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

1 Comment

Mikael Eriksson Over a year ago

@JasonC There is no need to cast to XML when you already have an XML column.

Collectives™ on Stack Overflow

Extracting a value from a XML column in SQL Server 2012

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related