Another XML Query - 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)

Another XML Query

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-27 : 05:38:46

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-27 : 09:21:47

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)

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-27 : 11:49:26

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>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:08:24

[code]
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

[/code]

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-29 : 11:43:35

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-29 : 14:15:42

partial search within what? you mean node value? then you can use contains() function for that

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-03-29 : 15:27:56

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-30 : 01:51:24

yep.
see
http://msdn.microsoft.com/en-IN/library/ms189254.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources