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)
 Another XML Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

177 Posts

Posted - 03/27/2013 :  05:38:46  Show Profile  Reply with Quote
Firstly , thank you all for your help. I have been trying to apply the previous help to this query but am struggling. What I want to return is the header records and just the details from originatingaccount where originatingAccount = @variable. In this case I only want the details where OriginatingAccount @number = '22222222'

My query

SELECT
h.c.value('@reportType[1]' , 'VARCHAR(20)') ,
h.c.value('@adviceNumber[1]' , 'VARCHAR(20)') ,
CONVERT( SMALLDATETIME, h.c.value('@currentProcessingDate[1]' , 'VARCHAR(20)') ),
CONVERT(SMALLDATETIME,(SELECT CRM_Processes.dbo.func_ProcessingDate_To_ClearedFundsDate((SELECT h.c.value('@currentProcessingDate[1]' , 'VARCHAR(10)')))), 120),
s.c.value('@userName[1]' , 'VARCHAR(50)') ,
s.c.value('@userNumber[1]' , 'VARCHAR(6)'),
NULL,
o.c.value('@number[1]' , 'VARCHAR(8)') ,
o.c.value('@sortCode[1]' , 'VARCHAR(8)') ,
o.c.value('@type[1]' , 'VARCHAR(2)') ,
o.c.value('@bankName[1]' , 'VARCHAR(50)'),
o.c.value('@branchName[1]' , 'VARCHAR(50)') ,
Null,
Null,
ISNULL(w.c.value('../@ref[1]' , 'VARCHAR(18)'), ''),
w.c.value('../@transCode[1]' , 'VARCHAR(2)'),
w.c.value('../@returnCode[1]' , 'VARCHAR(10)') ,
w.c.value('../@returnDescription[1]' , 'VARCHAR(20)') ,
w.c.value('../@originalProcessingDate[1]' , 'VARCHAR(10)') ,
CAST(w.c.value('../@valueOf[1]' , 'VARCHAR(13)')AS DECIMAL(11,2)) ,
w.c.value('../@currency[1]' , 'VARCHAR(3)') ,
w.c.value('@number[1]' , 'VARCHAR(8)') ,
w.c.value('@ref[1]' , 'VARCHAR(18)') ,
ISNULL(w.c.value('@name[1]' , 'VARCHAR(18)'), '') ,
w.c.value('@sortCode[1]' , 'VARCHAR(8)') ,
w.c.value('@bankName[1]' , 'VARCHAR(50)') ,
w.c.value('@branchName[1]' , 'VARCHAR(50)')
FROM @XMLVAR.nodes('//Data/ARUDD') AS a(c)
OUTER APPLY a.c.nodes('Header') h(c)
OUTER APPLY a.c.nodes('ServiceLicenseInformation') s(c)
OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/OriginatingAccount') o(c)
OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') w(c)

XML

<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2012-11-13"></Header>
<AddresseeInformation name="MY COMAPNY"></AddresseeInformation>
<ServiceLicenseInformation userName="MY COMAPNY" userNumber="000000"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="ACCOUNT1" number="11111111" sortCode="11-11-11" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="MY REF" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="1.00" currency="GBP"><PayerAccount number="99999999" ref="MY REf" name="TEST ACC 1" sortCode="22-22-22" bankName="HSBC BANK PLC" branchName="ENFIELD THE TOWN"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="1" valueOf="1.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="ACCOUNT2" number="22222222" sortCode="22-22-22" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="MY REF2" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="99.99" currency="GBP"><PayerAccount number="88888888" ref="MY REF2" name="TEST ACC 2" sortCode="44-44-44" bankName="HSBC BANK PLC" branchName="BERKHAMSTED"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="1" valueOf="99.99" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data><SignatureMethod></SignatureMethod><Signature></Signature></BACSDocument>

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 03/27/2013 :  09:21:47  Show Profile  Reply with Quote
Do you mean something like this? I am showing only one column from the header record and one column from the OriginatingAccount record; other columns can be retrieved the same way. If this is not it, can you list sample output that you want to get?
declare @variable varchar(256) = '22222222';
select
	c1.value('(Header/@reportType)[1]','varchar(256)') as ReportType,
	c2.value('(OriginatingAccount/@name)[1]','varchar(256)') as Name
from
	@xmlvar.nodes('BACSDocument/Data/ARUDD') T1(c1)
	cross apply c1.nodes 
		('Advice/OriginatingAccountRecords/OriginatingAccountRecord[OriginatingAccount/@number=sql:variable("@variable")]')T2(c2)
Go to Top of Page

Pete_N
Posting Yak Master

177 Posts

Posted - 03/27/2013 :  11:49:26  Show Profile  Reply with Quote
Hi, A bit premature, I though the job was done, however, in my test xml there is only one record under each of the originating account tags, but if there are more than 1 I am only get one returned .. My amended query is below as well as amended XML, in this case I would be looking for 2 records when the @TORIGACC is set to '22222222'

The only way I know is to load the header record into a temp table, and the details into a 2nd temp table and do a select * from #temp1, #temp2. I just wanted to know if there is a more efficient way to do it

SELECT
CAST(Newid() as VARCHAR(50)),
c1.value('(Header/@reportType)[1]','varchar(256)'),
c1.value('(Header/@adviceNumber)[1]' , 'VARCHAR(20)') ,
CONVERT( SMALLDATETIME, c1.value('(Header/@currentProcessingDate)[1]' , 'VARCHAR(20)') ),
CONVERT(SMALLDATETIME,(SELECT CRM_Processes.dbo.func_ProcessingDate_To_ClearedFundsDate(c1.value('(Header/@currentProcessingDate)[1]' , 'VARCHAR(20)'))), 120),
c1.value('(ServiceLicenseInformation/@userName)[1]' , 'VARCHAR(50)') ,
c1.value('(ServiceLicenseInformation/@userNumber)[1]' , 'VARCHAR(6)'),
NULL,
t2.c2.value('(OriginatingAccount/@number)[1]' , 'VARCHAR(8)') ,
t2.c2.value('(OriginatingAccount/@sortCode)[1]' , 'VARCHAR(8)') ,
t2.c2.value('(OriginatingAccount/@type)[1]' , 'VARCHAR(2)') ,
t2.c2.value('(OriginatingAccount/@bankName)[1]' , 'VARCHAR(50)'),
t2.c2.value('(OriginatingAccount/@branchName)[1]' , 'VARCHAR(50)') ,
Null,
Null,
ISNULL(t2.c2.value('(ReturnedDebitItem/@ref)[1]' , 'VARCHAR(18)'), ''),
t2.c2.value('(ReturnedDebitItem/@transCode)[1]' , 'VARCHAR(2)'),
t2.c2.value('(ReturnedDebitItem/@returnCode)[1]' , 'VARCHAR(10)') ,
t2.c2.value('(ReturnedDebitItem/@returnDescription)[1]' , 'VARCHAR(20)') ,
t2.c2.value('(ReturnedDebitItem/@originalProcessingDate)[1]' , 'VARCHAR(10)') ,
CAST(t2.c2.value('(ReturnedDebitItem/@valueOf)[1]' , 'VARCHAR(13)')AS DECIMAL(11,2)) ,
t2.c2.value('(ReturnedDebitItem/@currency)[1]' , 'VARCHAR(3)') ,
t2.c2.value('(ReturnedDebitItem/PayerAccount/@number)[1]' , 'VARCHAR(8)') ,
t2.c2.value('(ReturnedDebitItem/PayerAccount/@ref)[1]' , 'VARCHAR(18)') ,
ISNULL(t2.c2.value('(ReturnedDebitItem/PayerAccount/@name)[1]' , 'VARCHAR(18)'), '') ,
t2.c2.value('(ReturnedDebitItem/PayerAccount/@sortCode)[1]' , 'VARCHAR(8)') ,
t2.c2.value('(ReturnedDebitItem/PayerAccount/@bankName)[1]' , 'VARCHAR(50)') ,
t2.c2.value('(ReturnedDebitItem/PayerAccount/@branchName)[1]' , 'VARCHAR(50)')
FROM @xmlvar.nodes('BACSDocument/Data/ARUDD') T1(c1)
OUTER apply c1.nodes
('Advice/OriginatingAccountRecords/OriginatingAccountRecord[OriginatingAccount/@number=sql:variable("@TORIGACC")]')T2(c2)

<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2012-11-13"></Header>
<AddresseeInformation name="MY COMAPNY"></AddresseeInformation>
<ServiceLicenseInformation userName="MY COMAPNY" userNumber="000000"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="ACCOUNT1" number="11111111" sortCode="11-11-11" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="MY REF" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="1.00" currency="GBP">
<PayerAccount number="99999999" ref="MY REf" name="TEST ACC 1" sortCode="22-22-22" bankName="HSBC BANK PLC" branchName="ENFIELD THE TOWN"></PayerAccount>
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="1.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="ACCOUNT2" number="22222222" sortCode="22-22-22" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="MY REF2" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="99.99" currency="GBP">
<PayerAccount number="88888888" ref="MY REF2" name="TEST ACC 2" sortCode="44-44-44" bankName="HSBC BANK PLC" branchName="BERKHAMSTED"></PayerAccount>
</ReturnedDebitItem>
<ReturnedDebitItem ref="MY REF3" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="1.00" currency="GBP">
<PayerAccount number="55555555" ref="MY REF3" name="TEST ACC 3" sortCode="55-55-55" bankName="HSBC BANK PLC" branchName="BERKHAMSTED"></PayerAccount>
</ReturnedDebitItem>
<Totals numberOf="2" valueOf="100.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/28/2013 :  05:08:24  Show Profile  Reply with Quote

declare @x xml
declare @TORIGACC int =22222222
set @x='<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2012-11-13"></Header>
<AddresseeInformation name="MY COMAPNY"></AddresseeInformation>
<ServiceLicenseInformation userName="MY COMAPNY" userNumber="000000"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="ACCOUNT1" number="11111111" sortCode="11-11-11" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="MY REF" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="1.00" currency="GBP">
<PayerAccount number="99999999" ref="MY REf" name="TEST ACC 1" sortCode="22-22-22" bankName="HSBC BANK PLC" branchName="ENFIELD THE TOWN"></PayerAccount>
</ReturnedDebitItem>
<Totals numberOf="1" valueOf="1.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="ACCOUNT2" number="22222222" sortCode="22-22-22" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="MY REF2" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="99.99" currency="GBP">
<PayerAccount number="88888888" ref="MY REF2" name="TEST ACC 2" sortCode="44-44-44" bankName="HSBC BANK PLC" branchName="BERKHAMSTED"></PayerAccount>
</ReturnedDebitItem>
<ReturnedDebitItem ref="MY REF3" transCode="17" returnCode="1314" returnDescription="INSTRUCTION CANCELLED" originalProcessingDate="2012-11-09" valueOf="1.00" currency="GBP">
<PayerAccount number="55555555" ref="MY REF3" name="TEST ACC 3" sortCode="55-55-55" bankName="HSBC BANK PLC" branchName="BERKHAMSTED"></PayerAccount>
</ReturnedDebitItem>
<Totals numberOf="2" valueOf="100.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
<SignatureMethod></SignatureMethod>
<Signature></Signature>
</BACSDocument>'
SELECT @X
SELECT 
CAST(Newid() as VARCHAR(50)), 
c1.value('(//Header/@reportType)[1]','varchar(256)'), 
c1.value('(//Header/@adviceNumber)[1]' , 'VARCHAR(20)') , 
CONVERT( SMALLDATETIME, c1.value('(Header/@currentProcessingDate)[1]' , 'VARCHAR(20)') ),
--CONVERT(SMALLDATETIME,(SELECT CRM_Processes.dbo.func_ProcessingDate_To_ClearedFundsDate(c1.value('(Header/@currentProcessingDate)[1]' , 'VARCHAR(20)'))), 120),
c1.value('(//ServiceLicenseInformation/@userName)[1]' , 'VARCHAR(50)') ,
c1.value('(//ServiceLicenseInformation/@userNumber)[1]' , 'VARCHAR(6)'), 
NULL,
t2.c2.value('(../OriginatingAccount/@number)[1]' , 'VARCHAR(8)') , 
t2.c2.value('(../OriginatingAccount/@sortCode)[1]' , 'VARCHAR(8)') , 
t2.c2.value('(../OriginatingAccount/@type)[1]' , 'VARCHAR(2)') ,
t2.c2.value('(../OriginatingAccount/@bankName)[1]' , 'VARCHAR(50)'), 
t2.c2.value('(../OriginatingAccount/@branchName)[1]' , 'VARCHAR(50)') , 
Null, 
Null,
ISNULL(t2.c2.value('(@ref)[1]' , 'VARCHAR(18)'), ''),
t2.c2.value('(@transCode)[1]' , 'VARCHAR(2)'),
t2.c2.value('(@returnCode)[1]' , 'VARCHAR(10)') ,
t2.c2.value('(@returnDescription)[1]' , 'VARCHAR(20)') ,
t2.c2.value('(@originalProcessingDate)[1]' , 'VARCHAR(10)') ,
CAST(t2.c2.value('(@valueOf)[1]' , 'VARCHAR(13)')AS DECIMAL(11,2)) ,
t2.c2.value('(@currency)[1]' , 'VARCHAR(3)') ,
t2.c2.value('(./PayerAccount/@number)[1]' , 'VARCHAR(8)') ,
t2.c2.value('(./PayerAccount/@ref)[1]' , 'VARCHAR(18)') ,
ISNULL(t2.c2.value('(./PayerAccount/@name)[1]' , 'VARCHAR(18)'), '') ,
t2.c2.value('(./PayerAccount/@sortCode)[1]' , 'VARCHAR(8)') ,
t2.c2.value('(./PayerAccount/@bankName)[1]' , 'VARCHAR(50)') ,
t2.c2.value('(./PayerAccount/@branchName)[1]' , 'VARCHAR(50)') 
FROM @x.nodes('BACSDocument/Data/ARUDD') T1(c1)
OUTER apply c1.nodes 
('Advice/OriginatingAccountRecords/OriginatingAccountRecord[./OriginatingAccount/@number=sql:variable("@TORIGACC")]/ReturnedDebitItem')T2(c2)


output
---------------------------------------
280AE153-A936-4328-8ADD-2DAF3DC3567A	REFT1019	999	2012-11-13 00:00:00	MY COMAPNY	000000	NULL	22222222	22-22-22	0	HSBC BANK PLC	ST ALBANS	NULL	NULL	MY REF2	17	1314	INSTRUCTION CANCELLE	2012-11-09	99.99	GBP	88888888	MY REF2	TEST ACC 2	44-44-44	HSBC BANK PLC	BERKHAMSTED
27A838BC-F56B-489A-A3BB-8EEEF16020A8	REFT1019	999	2012-11-13 00:00:00	MY COMAPNY	000000	NULL	22222222	22-22-22	0	HSBC BANK PLC	ST ALBANS	NULL	NULL	MY REF3	17	1314	INSTRUCTION CANCELLE	2012-11-09	1.00	GBP	55555555	MY REF3	TEST ACC 3	55-55-55	HSBC BANK PLC	BERKHAMSTED



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

Go to Top of Page

Pete_N
Posting Yak Master

177 Posts

Posted - 03/29/2013 :  11:43:35  Show Profile  Reply with Quote
Hi, that's great thank you. Just for future reference, if I needed to do a partial search
ie in SQL, Left(Field , length) = ? how would that be applied to

OUTER apply c1.nodes
('Advice/OriginatingAccountRecords/OriginatingAccountRecord[./OriginatingAccount/@number=sql:variable("@TORIGACC")]/ReturnedDebitItem')T2(c2)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/29/2013 :  14:15:42  Show Profile  Reply with Quote
partial search within what? you mean node value? then you can use contains() function for that

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

Go to Top of Page

Pete_N
Posting Yak Master

177 Posts

Posted - 03/29/2013 :  15:27:56  Show Profile  Reply with Quote
In this instance I would be looking for @number contains @TORIGACC.
Do u know the best place to look up these functions with examples of how to apply them
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/30/2013 :  01:51:24  Show Profile  Reply with Quote
yep.
see
http://msdn.microsoft.com/en-IN/library/ms189254.aspx

------------------------------------------------------------------------------------------------------
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.11 seconds. Powered By: Snitz Forums 2000