Reading Xml File - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Reading Xml File

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-04 : 10:28:27

Xml File - I have changed the data for security
<BACSDocument >
<Data>
<InputReport>
<Header ></ProducedOn><ProcessingDate></ProcessingDate></Header>
<Submission>
<SubmissionInformation ></SubmissionInformation>
<UserFile fileLevelMessage="">
<InputUserFile>
<Errors>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="123456" transactionCode="01" currency="GBP" valueOf="1.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="888888" name="THEIR NAME" sortCode="222222" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="654321" transactionCode="01" currency="GBP" valueOf="2.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="77777777" name="J SMITH" sortCode="333333" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT"><MessageLine>Individual Item Limit Exceeded</MessageLine></ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="ORIGINAL RECORD" reference="987654" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="99999999" ref="MYREF" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="55555555" name="FRED FLINTSTONE" sortCode="666666" type="0"></DestinationAccount>
</ErrorItem>
<ErrorItem errorCode="E" errorItemType="RETURNED RECORD" reference="MYREF" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="55555555" ref=" 335" name="FRED FLINTSTONE" sortCode="666666"></OriginatingAccount>
<DestinationAccount number="99999999" name="MY NAME" sortCode="111111" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="REASON"><MessageLine>Recipient's sort code is invalid</MessageLine></ErrorMessage>
</Error>
<TotalNumberOfErrors amendedRecords="1" returnedRecords="1" rejectedRecords="0"></TotalNumberOfErrors>
</Errors>
</InputUserFile>
</UserFile>
</Submission>
</InputReport>
</Data>
</BACSDocument>

SQL so far
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARMS NVARCHAR(1000)
DECLARE @XMLVAR XML

SET @SQL = '(SELECT @XMLVAR = x.c FROM OPENROWSET(BULK ''' + @FileName + ''', SINGLE_BLOB) AS x(c))'
SET @PARMS = '@XMLVAR XML OUTPUT'
EXEC sp_executesql @SQL , @PARMS, @XMLVAR OUTPUT

SELECT
Err.Errors.value('(//Data/InputReport/Submission/UserFile/InputUserFile/Errors/Error/ErrorMessage/@type)[1]','varchar(50) ' ) as 'errorType',
Err.Errors.value('(//Data/InputReport/Submission/UserFile/InputUserFile/Errors/Error/ErrorMessage/MessageLine)[1]','varchar(100) ' ) as 'MessageLine',
Err.Errors.value('(@errorItemType)[1]','varchar(100) ' ) as 'errorItemType',
Err.Errors.value('(@reference)[1]','varchar(18) ' ) as 'reference',
Err.Errors.value('(@transactionCode)[1]','varchar(2) ' ) as 'transactionCode',
Err.Errors.value('(@currency)[1]','varchar(3) ' ) as 'currency',
Err.Errors.value('(@valueOf)[1]','varchar(32) ' ) as 'valueOf',
Err.Errors.value('(@errorCode)[1]','varchar(100)') as 'errorCode',
Orig.OrigVals.value('(@number)[1]','varchar(8) ' ) as 'OAnumber',
Orig.OrigVals.value('(@ref)[1]','varchar(18) ' ) as 'OAref',
Orig.OrigVals.value('(@name)[1]','varchar(18) ' ) as 'OAname',
Orig.OrigVals.value('(@sortCode)[1]','varchar(8) ' ) as 'OAsortCode',
Dest.DestVals.value('(@number)[1]','varchar(8) ' ) as 'DAnumber',
Dest.DestVals.value('(@name)[1]','varchar(18) ' ) as 'DAname',
Dest.DestVals.value('(@sortCode)[1]','varchar(8) ' ) as 'DAsortCode',
Dest.DestVals.value('(@type)[1]','varchar(10) ' ) as 'DAtype'

FROM @xmlvar.nodes('//Data/InputReport/Submission/UserFile/InputUserFile/Errors/Error/ErrorItem') Err(Errors)
CROSS APPLY Errors.nodes('OriginatingAccount') Orig(OrigVals)
CROSS APPLY Errors.nodes('DestinationAccount') Dest(DestVals)

It works to a degree. Its the last record that I am struggling with. I want the original record and returned record to appear as one record in the select query.

I know that there will be a load of NULL values for the 'Highlighted Records, but that fine as this will be going into a temp table first.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 12:02:06

The XML you posted is not well-formed - in the first four lines, there are at least two problems:

<BACSDocument >  no matching end tag for <BACSDocument >
<Data>
<InputReport>
<Header ></ProducedOn><ProcessingDate></ProcessingDate></Header>
<Submission>

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-04 : 12:29:07

My apoliguies, I have had to edit the file due to the sensitive nature of the data.
<BACSDocument >
<Data>
<InputReport>
<Header>
<ProducedOn></ProducedOn>
<ProcessingDate></ProcessingDate>
</Header>
<Submission>
<SubmissionInformation ></SubmissionInformation>
<UserFile fileLevelMessage=""/>
<InputUserFile>
<Errors>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="123456" transactionCode="01" currency="GBP" valueOf="1.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="888888" name="THEIR NAME" sortCode="222222" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="654321" transactionCode="01" currency="GBP" valueOf="2.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="77777777" name="J SMITH" sortCode="333333" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="ORIGINAL RECORD" reference="987654" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="99999999" ref="MYREF" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="55555555" name="FRED FLINTSTONE" sortCode="666666" type="0"></DestinationAccount>
</ErrorItem>
<ErrorItem errorCode="E" errorItemType="RETURNED RECORD" reference="MYREF" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="55555555" ref=" 335" name="FRED FLINTSTONE" sortCode="666666"></OriginatingAccount>
<DestinationAccount number="99999999" name="MY NAME" sortCode="111111" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="REASON">
<MessageLine>Recipient's sort code is invalid</MessageLine>
</ErrorMessage>
</Error>
<TotalNumberOfErrors amendedRecords="1" returnedRecords="1" rejectedRecords="0"></TotalNumberOfErrors>
</Errors>
</InputUserFile>
</UserFile>
</Submission>
</InputReport>
</Data>
</BACSDocument>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 13:39:52

Pete, still not good. I tried to fix it, but since I don't know the structure of the XML, I found it hard to do. Run the following code after replacing the placeholder I have with your XML fragment and post it after fixing any errors:

DECLARE @xmlvar XML = 'Put your XML Fragment here. 
	If there are single quotes, escape with another quote,
	for example, Recipient''s sort code is invalid';
SELECT @xmlvar;

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-04 : 13:53:10

Hi James, one / in the wrong place .. I need a holiday . anyway amended code and tested in TSQL

<BACSDocument >
<Data>
<InputReport>
<Header>
<ProducedOn>
</ProducedOn>
<ProcessingDate>
</ProcessingDate>
</Header>
<Submission>
<SubmissionInformation >
</SubmissionInformation>
<UserFile fileLevelMessage="">
<InputUserFile>
<Errors>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="123456" transactionCode="01" currency="GBP" valueOf="1.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="888888" name="THEIR NAME" sortCode="222222" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="654321" transactionCode="01" currency="GBP" valueOf="2.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="77777777" name="J SMITH" sortCode="333333" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="ORIGINAL RECORD" reference="987654" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="99999999" ref="MYREF" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="55555555" name="FRED FLINTSTONE" sortCode="666666" type="0"></DestinationAccount>
</ErrorItem>
<ErrorItem errorCode="E" errorItemType="RETURNED RECORD" reference="MYREF" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="55555555" ref=" 335" name="FRED FLINTSTONE" sortCode="666666"></OriginatingAccount>
<DestinationAccount number="99999999" name="MY NAME" sortCode="111111" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="REASON">
<MessageLine>Recipient's sort code is invalid</MessageLine>
</ErrorMessage>
</Error>
<TotalNumberOfErrors amendedRecords="1" returnedRecords="1" rejectedRecords="0"></TotalNumberOfErrors>
</Errors>
</InputUserFile>
</UserFile>
</Submission>
</InputReport>
</Data>
</BACSDocument>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 14:34:50

Much better :)
But, I didn't 100% follow what the requirement is. In the following, I am trying to get just one column - OANumber - side by side. If that is the type of thing you are looking for, that can be done. But, then what needs to be done about the elements with the HIGHLIGHT RECORD?

SELECT
	Errors.value('(ErrorItem[@errorItemType="ORIGINAL RECORD"]/OriginatingAccount/@number)[1]','varchar(8)') as 'OAnumberOriginal',
	Errors.value('(ErrorItem[@errorItemType="RETURNED RECORD"]/OriginatingAccount/@number)[1]','varchar(8)') as 'OAnumberReturned'
FROM 
	@xmlvar.nodes('//Data/InputReport/Submission/UserFile/InputUserFile/Errors/Error') Err(Errors)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 23:41:33

here's my illustration based on your latest posted XML


declare @test xml

set @test='<BACSDocument>
<Data>
<InputReport>
<Header> 
<ProducedOn>
</ProducedOn>
<ProcessingDate>
</ProcessingDate>
</Header>
<Submission>
<SubmissionInformation >
</SubmissionInformation>
<UserFile fileLevelMessage="">
<InputUserFile>
<Errors>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="123456" transactionCode="01" currency="GBP" valueOf="1.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="888888" name="THEIR NAME" sortCode="222222" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="HIGHLIGHT RECORD" reference="654321" transactionCode="01" currency="GBP" valueOf="2.00">
<OriginatingAccount number="99999999" ref=" 335" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="77777777" name="J SMITH" sortCode="333333" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="HIGHLIGHT">
<MessageLine>Individual Item Limit Exceeded</MessageLine>
</ErrorMessage>
</Error>
<Error>
<ErrorItem errorItemType="ORIGINAL RECORD" reference="987654" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="99999999" ref="MYREF" name="MY NAME" sortCode="111111"></OriginatingAccount>
<DestinationAccount number="55555555" name="FRED FLINTSTONE" sortCode="666666" type="0"></DestinationAccount>
</ErrorItem>
<ErrorItem errorCode="E" errorItemType="RETURNED RECORD" reference="MYREF" transactionCode="17" currency="GBP" valueOf="100.00">
<OriginatingAccount number="55555555" ref=" 335" name="FRED FLINTSTONE" sortCode="666666"></OriginatingAccount>
<DestinationAccount number="99999999" name="MY NAME" sortCode="111111" type="0"></DestinationAccount>
</ErrorItem>
<ErrorMessage type="REASON">
<MessageLine>Recipient''s sort code is invalid</MessageLine>
</ErrorMessage>
</Error>
<TotalNumberOfErrors amendedRecords="1" returnedRecords="1" rejectedRecords="0"></TotalNumberOfErrors>
</Errors>
</InputUserFile>
</UserFile>
</Submission>
</InputReport>
</Data>
</BACSDocument>'

SELECT t.u.value('(./ErrorMessage/@type)[1]','varchar(50)') AS ErrorType,
t.u.value('(./ErrorMessage/MessageLine)[1]','varchar(2000)') AS MessageLine,
m.n.value('(./@errorItemType)[1]','varchar(100)') AS errorItemType,
m.n.value('(./@reference)[1]','varchar(50)') AS reference,
m.n.value('(./@transactionCode)[1]','varchar(50)') AS transactionCode,
m.n.value('(./@currency)[1]','varchar(50)') AS currency,
m.n.value('(./@valueOf)[1]','varchar(50)') AS valueOf,
m.n.value('(./@errorCode)[1]','varchar(50)') AS errorCode,
m.n.value('(./OriginatingAccount/@number)[1]','int') AS OrgNo,
m.n.value('(./DestinationAccount[1]/@number)[1]','int') AS DestNo,
m.n.value('(./OriginatingAccount/@ref)[1]','varchar(50)') AS OrgRef,
m.n.value('(./DestinationAccount[1]/@ref)[1]','varchar(50)') AS DestRef,
m.n.value('(./OriginatingAccount/@name)[1]','varchar(50)') AS OrgName,
m.n.value('(./DestinationAccount[1]/@name)[1]','varchar(50)') AS DestName,
m.n.value('(./OriginatingAccount/@sortCode)[1]','int') AS OrgSortCode,
m.n.value('(./DestinationAccount[1]/@sortCode)[1]','int') AS DestSortCode,
m.n.value('(./DestinationAccount[1]/@type)[1]','int') AS DestType
FROM @test.nodes('/BACSDocument/Data/InputReport/Submission/UserFile/InputUserFile/Errors/Error')t(u)
CROSS APPLY t.u.nodes('ErrorItem')m(n)


output
-------------------------------------------
ErrorType	MessageLine	errorItemType	reference	transactionCode	currency	valueOf	errorCode	OrgNo	DestNo	OrgRef	DestRef	OrgName	DestName	OrgSortCode	DestSortCode	DestType
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HIGHLIGHT	Individual Item Limit Exceeded	HIGHLIGHT RECORD	123456	01	GBP	1.00	NULL	99999999	888888	 335	NULL	MY NAME	THEIR NAME	111111	222222	0
HIGHLIGHT	Individual Item Limit Exceeded	HIGHLIGHT RECORD	654321	01	GBP	2.00	NULL	99999999	77777777	 335	NULL	MY NAME	J SMITH	111111	333333	0
REASON	Recipient's sort code is invalid	ORIGINAL RECORD	987654	17	GBP	100.00	NULL	99999999	55555555	MYREF	NULL	MY NAME	FRED FLINTSTONE	111111	666666	0
REASON	Recipient's sort code is invalid	RETURNED RECORD	MYREF	17	GBP	100.00	E	55555555	99999999	 335	NULL	FRED FLINTSTONE	MY NAME	666666	111111	0

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-05 : 04:50:09

Thank you both for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 04:53:37

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources