help 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)

help reading xml file

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-23 : 14:44:34

I have an xml file that I want to be able to read only one section dependant on a variable

XML
<?xml version="1.0" encoding="ISO-8859-1"?>

<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2013-03-12"></Header>
<AddresseeInformation name="BUREAU LTD"></AddresseeInformation>
<ServiceLicenseInformation userName="BUREAU LTD" userNumber="940905"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="TEST1" number="11111111" sortCode="11-11-11" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="XXXXXXXX " transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" originalProcessingDate="2013-03-08" valueOf="1.00" currency="GBP"><PayerAccount number="99999999" ref="XXXXXXXX" name="CLIENT1" sortCode="99-99-99" bankName="HSBC BANK PLC" branchName="NEATH"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="XXXXXXXX " transCode="17" returnCode="6067" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-03-08" valueOf="2.00" currency="GBP"><PayerAccount number="88888888" ref="XXXXXXXXX" name="CLIENT2" sortCode="88-88-88" bankName="BARCLAYS BANK PLC" branchName="WATFORD"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="XXXXXXXX " transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-03-08" valueOf="3.00" currency="GBP"><PayerAccount number="77777777" ref="XXXXXXXX" name="CLIENT3" sortCode="77-77-77" bankName="HSBC BANK PLC" branchName="HITCHIN"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="3" valueOf="6.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="COMPANY 2" number="33333333" sortCode="33-22-33" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="90188 " transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-03-08" valueOf="60.65" currency="GBP"><PayerAccount number="44444444" ref="90188 " name="CLIENT4" sortCode="44-44-44" bankName="HSBC BANK PLC" branchName="LONDON BRIDGE"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="89905 " transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" originalProcessingDate="2013-03-08" valueOf="161.46" currency="GBP"><PayerAccount number="55555555" ref="89905 " name="CLIENT5" sortCode="55-55-55" bankName="Santander" branchName="BANK ACCOUNT 3"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="90390 " transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" originalProcessingDate="2013-03-08" valueOf="152.34" currency="GBP"><PayerAccount number="55555555" ref="90390 " name="CLIENT5" sortCode="55-55-55" bankName="Santander" branchName="BANK ACCOUNT 3"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="89941 " transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-03-08" valueOf="67.92" currency="GBP"><PayerAccount number="66666666" ref="89941 " name="CLIENT6" sortCode="66-66-66" bankName="HSBC BANK PLC" branchName="KEYNSHAM"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="90426 " transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2013-03-08" valueOf="72.28" currency="GBP"><PayerAccount number="66666666" ref="90426 " name="CLIENT6" sortCode="66-66-66" bankName="HSBC BANK PLC" branchName="KEYNSHAM"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="5" valueOf="514.65" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data><SignatureMethod>Vanilla</SignatureMethod><Signature></Signature></BACSDocument>

Query so far, returns all records

DECLARE @FileName varchar(250) = 'Text.xml'
SET NOCOUNT ON;
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
a.d.value('../@ref[1]' , 'VARCHAR(18)'),
a.d.value('../@transCode[1]' , 'VARCHAR(2)'),
a.d.value('../@returnCode[1]' , 'VARCHAR(10)') ,
a.d.value('../@returnDescription[1]' , 'VARCHAR(20)') ,
a.d.value('../@originalProcessingDate[1]' , 'VARCHAR(10)') ,
CAST(a.d.value('../@valueOf[1]' , 'VARCHAR(13)')AS DECIMAL(11,2)) ,
a.d.value('../@currency[1]' , 'VARCHAR(3)') ,
a.d.value('@number[1]' , 'VARCHAR(8)') ,
a.d.value('@ref[1]' , 'VARCHAR(18)') ,
ISNULL(a.d.value('@name[1]' , 'VARCHAR(18)') , ''),
a.d.value('@sortCode[1]' , 'VARCHAR(8)') ,
a.d.value('@bankName[1]' , 'VARCHAR(50)') ,
a.d.value('@branchName[1]' , 'VARCHAR(50)')
FROM @XMLVAR.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') AS a(d)

What I am trying to do is add a variable so that I only return the section where <OriginatingAccount number="33333333"> = @variable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-23 : 15:06:32

This?
http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx#related-results

N 56°04'39.26"
E 12°55'05.63"

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-23 : 15:21:20

HI,

I have spent a few hours trying all differnet things and either end up with no records returned or all records, I can no work out how to apply the selection to //Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord number

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-23 : 15:29:00

[code]DECLARE @Search VARCHAR(18) = '33333333';

SELECT n.value('(../@ref)', 'VARCHAR(18)'),
n.value('(../@transCode)', 'VARCHAR(2)'),
n.value('(../@returnCode)', 'VARCHAR(10)') ,
n.value('(../@returnDescription)', 'VARCHAR(20)') ,
n.value('(../@originalProcessingDate)', 'VARCHAR(10)') ,
n.value('(../@valueOf)', 'DECIMAL(11,2)'),
n.value('(../@currency)', 'VARCHAR(3)') ,
n.value('(@number)', 'VARCHAR(8)') ,
n.value('(@ref)', 'VARCHAR(18)') ,
ISNULL(n.value('(@name)', 'VARCHAR(18)') , ''),
n.value('(@sortCode)', 'VARCHAR(8)') ,
n.value('(@bankName)', 'VARCHAR(50)') ,
n.value('(@branchName)', 'VARCHAR(50)')
FROM @XMLVAR.nodes('(/BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord[OriginatingAccount/@number = sql:variable("@Search")]/ReturnedDebitItem/PayerAccount)') AS d(n)[/code]

N 56°04'39.26"
E 12°55'05.63"

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-23 : 15:39:54

Brilliant, I understand now. Thank you for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources