How to load xml elements containing attributes in sql server

Question

I'm using SQL Server 2008 and I'm having trouble getting all data out of this xml:

DECLARE @xml xml

SELECT @xml =
'<dlines id="1234">
  <dline time="16h49" order="1">bladibla</dline> 
  <dline time="16h50" order="2">more bladibla</dline> 
</dlines>'

I can get the values of the attributes of the dline element using:

SELECT  TimeStr = dLine.value('@time', 'nvarchar(20)'),
OrderNo = dLine.value('@order', 'int'),
StatusStr = dLine.value('@status', 'nvarchar(20)'),
PeriodStr = dLine.value('@period', 'nvarchar(20)')
FROM    @afpxml.nodes('/dlines/dline') as XTbl(dLine)

But how do I get the value of dline element itself (the 'bladibla1')?

Thnx a lot for your help!

Andomar · Accepted Answer · 2013-11-28 13:59:34Z

1

Just query the value of ., the current node:

DlineItself = dLine.value('.', 'nvarchar(20)')

answered Nov 28, 2013 at 13:59

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Collectives™ on Stack Overflow

How to load xml elements containing attributes in sql server

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