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)
 help reading xml file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

176 Posts

Posted - 03/23/2013 :  14:44:34  Show Profile  Reply with Quote
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"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<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

Sweden
30242 Posts

Posted - 03/23/2013 :  15:06:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Pete_N
Posting Yak Master

176 Posts

Posted - 03/23/2013 :  15:21:20  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 03/23/2013 :  15:29:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Pete_N
Posting Yak Master

176 Posts

Posted - 03/23/2013 :  15:39:54  Show Profile  Reply with Quote
Brilliant, I understand now. Thank you for your help
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.08 seconds. Powered By: Snitz Forums 2000