SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Reading Xml File
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

173 Posts

Posted - 03/04/2013 :  10:28:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/04/2013 :  12:02:06  Show Profile  Reply with Quote
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>
Go to Top of Page

Pete_N
Posting Yak Master

173 Posts

Posted - 03/04/2013 :  12:29:07  Show Profile  Reply with Quote
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>

Edited by - Pete_N on 03/04/2013 13:08:00
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/04/2013 :  13:39:52  Show Profile  Reply with Quote
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;
Go to Top of Page

Pete_N
Posting Yak Master

173 Posts

Posted - 03/04/2013 :  13:53:10  Show Profile  Reply with Quote
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>
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/04/2013 :  14:34:50  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/04/2013 :  23:41:33  Show Profile  Reply with Quote
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/

Go to Top of Page

Pete_N
Posting Yak Master

173 Posts

Posted - 03/05/2013 :  04:50:09  Show Profile  Reply with Quote
Thank you both for all your help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/05/2013 :  04:53:37  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000