CODEWITHSUNDEEP
sql-serverxml
Amorphous Blob
Amorphous Blob

Reputation: 215

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

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!

Upvotes: 1

Views: 2905

Answers (1)

granadaCoder
granadaCoder

Reputation: 27852

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)

Upvotes: 2

Related Questions