How to parse XML in TSQL

Question

I'm getting xml in SQL Server, and I need to get businessDate value

I tried this

Select C.value('(businessDate)[1]', 'DATETIME') AS 
FROM @input.nodes('/MealsUsed/*') AS T(C)

xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<MealsUsed businessDate="10/09/2013" count="10" institutionID="Test">
<MealUsed>
    <TransactionDate>02/05/2013 08:10:06</TransactionDate>
    <LocationID>8</LocationID>
    <Swipes>1</Swipes>
</MealUsed>
<MealUsed>

Also, can we assume that you actually have a name between the `AS` and the `FROM`? Or is that the problem? — RBarryYoung, Nov 15 '13 at 18:47

Roman Pekar · Answer 1 · 2013-11-15T18:56:08.457

businessDate is attribute, not element, so you have to use it with @:

select
    C.value('@businessDate', 'datetime') as businessDate
from @input.nodes('MealsUsed') as T(C)

Actually, since businessDate is not in xml-date format, i'd be better to get it to varchar and then convert with appropriate format:

select
    convert(datetime, C.value('@businessDate', 'varchar(10)'), 103) as businessDate
from @input.nodes('MealsUsed') as T(C)

sql fiddle demo

How to parse XML in TSQL

1 Answers1