Last XML question ( hopefully)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Last XML question ( hopefully)

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-16 : 15:12:01

in some of my xml files i have error sections. What i am trying to do is to put the errors into #table, but one error per record. I have had help reading simple xml files and managed to put the data into tables, but this is more complicated

xml example

<?xml version="1.0" encoding="UTF-8"?>

<Document>
<Data>
<Report>
<Sub>
<File fileLevelMessage="">
<UserFile>
<Errors>
<Error>
<ErrorItem errorItemType="ORIGINAL RECORD" reference="ABC" transactionCode="99" currency="GBP" valueOf="1.00">
<OriginatingAccount number="99999999" ref="XXXX" name="AN OTHER" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="88888888" name="MR SMITH" sortCode="222222" type="0"></DestinationAccount>
</ErrorItem>
<ErrorItem errorCode="B" errorItemType="AMENDED RECORD" reference="765PE001597371211 " transactionCode="99" currency="GBP" valueOf="1.00">
<OriginatingAccount number="99999999" ref="XXXX" name="AN OTHER" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="12345678" name="MR SMITH" sortCode="000000" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="REASON">
<MessageLine>Your account details are invalid</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="ORIGINAL RECORD" reference="DEF" transactionCode="99" currency="GBP" valueOf="1.00">
<OriginatingAccount number="77777777" ref="XXXX" name="AN OTHER" sortCode="444444"></OriginatingAccount>
<DestinationAccount number="66666666" name="MR SMITH" sortCode="333333" type="0"></DestinationAccount>
</ErrorItem>
<ErrorItem errorCode="B" errorItemType="AMENDED RECORD" reference="765PE001597371211 " transactionCode="99" currency="GBP" valueOf="1.00">
<OriginatingAccount number="77777777" ref="XXXX" name="AN OTHER" sortCode="444444"></OriginatingAccount>
<DestinationAccount number="22222222" name="MR SMITH" sortCode="999999" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="REASON">
<MessageLine>Your account details are invalid</MessageLine>
</ErrorMessage>
</Error>
<TotalNumberOfErrors amendedRecords="2" returnedRecords="0" rejectedRecords="0"></TotalNumberOfErrors>
</Errors>
</UserFile>
</File>
</Sub>
</Report>
</Data>
</Document>

Im looking for this sort of result

errorItemType reference transactionCode currency valueOf number ref name sortCode number name sortCode type errorCode errorItemType reference transactionCode currency valueOf number ref name sortCode number name sortCode type type
ORIGINAL RECORD ABC 99 GBP 1 99999999 XXXX AN OTHER 111111 88888888 MR SMITH 222222 0 B AMENDED RECORD ABC 99 GBP 1.00 99999999 XXXX AN OTHER 111111 12345678 MR SMITH 000000 0 REASON Your account details are invalid
ORIGINAL RECORD DEF 99 GBP 1.00 77777777 XXXX AN OTHER 444444 66666666 MR SMITH 333333 0 B AMENDED RECORD DEF 99 GBP 1.00 77777777 XXXX AN OTHER 444444 22222222 MR SMITH 999999 0 REASON Your account details are invalid

i know the field names are invalid, but when doing the select statement i can change the field name s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 16:37:27

this should give you a start



select
m.n.value('local-name(.)[1]','varchar(100)'),
m.n.value('.','varchar(100)'),
p.q.value('local-name(.)[1]','varchar(100)'),
p.q.value('.','varchar(100)')
FROM @x.nodes('/Document/Data/Report/Sub/File/UserFile/Errors/Error/ErrorItem') a(b)
CROSS APPLY b.nodes('./*/@*')p(q)
 CROSS APPLY b.nodes('./@*')m(n)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources