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>
Go to Top of Page

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>
Go to Top of Page

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;
Go to Top of Page

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>
Go to Top of Page

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)
Go to Top of Page

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/

Go to Top of Page

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-05 : 04:50:09
Thank you both for all your help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -