XML to SQL Server 2008 extract

Question

I need further assistance with extracting records from XML and loading it into a SQL Server table.

I have this as my @xml:

<admin submitter_id="login0" effective_date="mm/dd/yyyy">
<rec effected_id="login1" adjustment="100.00" type="foo">
<reason reason_id="1" />
<reason reason_id="2" />
</rec>
<rec effected_id="login2" adjustment="50.00" type="bar">
<reason reason_id="3" />
</rec>
</admin>

I need this from a result set:

login0, login1, mm/dd/yyyy, 100.00, foo, 1
login0, login1, mm/dd/yyyy, 100.00, foo, 2
login0, login2, mm/dd/yyyy, 50.00, bar, 3

Does that make sense? The adjustment to the reason_id is one to many. I have figured out how to extract all the values except for the second line. I can only obtain the first reason_id and then it proceeds to the next record. I think this can be beaten with a CROSS APPLY but I cannot get it to work. Please help!

oh, I may also have received bogus XML. So if that's wrong, please tell me!

Adriaan Stander · Accepted Answer · 2012-10-08 05:00:11Z

1

How about something like

DECLARE @Xml XML

SELECT @Xml = '<admin submitter_id="login0" effective_date="mm/dd/yyyy"> 
<rec effected_id="login1" adjustment="100.00" type="foo"> 
<reason reason_id="1" /> 
<reason reason_id="2" /> 
</rec> 
<rec effected_id="login2" adjustment="50.00" type="bar"> 
<reason reason_id="3" /> 
</rec> 
</admin>'

SELECT  @Xml,
        A2.B.value('(../../@submitter_id)[1]','VARCHAR(50)'),
        A2.B.value('(../@effected_id)[1]','VARCHAR(50)'),
        A2.B.value('(../../@effective_date)[1]','VARCHAR(50)'),
        A2.B.value('(../@adjustment)[1]','FLOAT'),
        A2.B.value('(../@type)[1]','VARCHAR(50)'),
        A2.B.value('(@reason_id)[1]','INT')
FROM    @XML.nodes('//admin/rec/reason')  A2(B)

SQL Fiddle DEMO

answered Oct 8, 2012 at 5:00

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Comments

marc_s · Accepted Answer · 2012-10-08 04:58:28Z

0

Try to use this T-SQL code snippet:

SELECT
    Submitter = @xml.value('(/admin/@submitter_id)[1]', 'varchar(50)'),
    EffectedID = Rec.value('(@effected_id)[1]', 'varchar(50)'),
    DateStamp = @xml.value('(/admin/@effective_date)[1]', 'varchar(50)'),
    TypeID = Rec.value('(@type)[1]', 'varchar(50)'),
    ReasonID = Reason.value('(@reason_id)[1]', 'int')
FROM
    @xml.nodes('/admin/rec') AS Tbl(Rec)
CROSS APPLY
    Rec.nodes('reason') AS T2(Reason)

It gives me an output of:

enter image description here

You need to have two nested lists of nodes - the first one grabs all the <rec> nodes from inside <admin>, while the second one iterates over all the <reason> nodes inside each of the <rec> nodes. That way, you can reliably extract all information from those two nested levels of subnodes, no matter how many subnodes there are on each level.

answered Oct 8, 2012 at 4:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

Matt Over a year ago

These are both great! Thanks very much! I can only accept one answer but both work equally well for what I need.

marc_s Over a year ago

@Matt: yeah - you can only accept one (which is hard to decide sometimes) - but you could at least upvote all those good answers! No limit on that ...

Collectives™ on Stack Overflow

XML to SQL Server 2008 extract

2 Answers 2

SQL Fiddle DEMO

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related