SQL Server 2012 - How to extract a value from an XML string?

Question

This seems very basic, but I haven't been able to find an example that works for me, so I'd appreciate any advice.

I have a SQL Server function that determines various dates based on our fiscal year and today's date, and returns one row which looks like... <row LastDayPrevMonth="2015-04-30T00:00:00" LastDayPrevMonthLY="2014-04-30T00:00:00" ... />

In the stored proc which calls that function, I've done...

DECLARE @X XML
SET @X = dbo.GetFiscalYearDates()

...but then I can't seem to extract the value of LastDayPrevMonth.

I've tried dozens of variations of this: SELECT ROW.ITEM.VALUE('LastDayPrevMonth', 'VARCHAR(30)')[1] AS Foo FROM @x.nodes('row/item') ... sometimes with an "AS Bar" at the end...

That particular syntax gives the error "incorrect syntax near the keywork 'as'", but any tweaks I do don't help.

Thanks for your assistance, dudes!

granadaCoder · Accepted Answer · 2015-06-04 14:19:22Z

2

declare @doc xml

select @doc= '
<root>
<row LastDayPrevMonth="2015-04-30T00:00:00" LastDayPrevMonthLY="2014-04-30T00:00:00" />
</root>
'

SELECT 
      LastDayPrevMonth = Y.i.value('(@LastDayPrevMonth)[1]', 'datetime')
    , LastDayPrevMonthLY = Y.i.value('@LastDayPrevMonthLY[1]', 'datetime') 

FROM 
    @doc.nodes('root/row') AS Y(i)

answered Jun 4, 2015 at 14:19

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

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

1 Comment

Amorphous Blob Over a year ago

I appreciate it granadaCoder; I can't vote you up because I don't have 15 points earned. For future readers, the function which returns the XML has to have the clause on the end: ... FOR XML RAW, ROOT('root') to get the root node in there.

Collectives™ on Stack Overflow

SQL Server 2012 - How to extract a value from an XML string?

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