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)
 records x 4
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

156 Posts

Posted - 01/25/2013 :  20:01:59  Show Profile  Reply with Quote
I have an xml file with four records in it

the following section of code returns each record 4 times, instead of just 4 records I am getting a result with 16 records

SELECT T2.c1.value('@ref[1]' , 'VARCHAR(18)') as 'RDI_ref',
T2.c1.value('@transCode[1]' , 'VARCHAR(2)') as 'RDI_transCode',
T2.c1.value('@returnCode[1]' , 'VARCHAR(10)') as 'RDI_returnCode',
T2.c1.value('@returnDescription[1]' , 'VARCHAR(50)') as 'RDI_returnDescription',
T2.c1.value('@originalProcessingDate[1]' , 'VARCHAR(10)') as 'RDI_originalProcessingDate',
T2.c1.value('@valueOf[1]' , 'VARCHAR(13)') as 'RDI_valueOf',
T2.c1.value('@currency[1]' , 'VARCHAR(3)') as 'RDI_currency',
T1.c1.value('@number[1]' , 'VARCHAR(8)') as 'PA_number',
T1.c1.value('@ref[1]' , 'VARCHAR(18)') as 'PA_ref',
T1.c1.value('@name[1]' , 'VARCHAR(18)') as 'PA_name',
T1.c1.value('@sortCode[1]' , 'VARCHAR(8)') as 'PA_sortCode',
T1.c1.value('@bankName[1]' , 'VARCHAR(50)') as 'PA_bankName',
T1.c1.value('@branchName[1]' , 'VARCHAR(50)') as 'PA_branchName'
FROM @XMLVAR.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') AS T1(c1)
OUTER APPLY T1.c1.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem') AS T2(c1)

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/26/2013 :  00:08:00  Show Profile  Reply with Quote
thats because of OUTER APPLY. I think what you need is this


SELECT T1.c1.value('@ref[1]' , 'VARCHAR(18)') as 'RDI_ref',
T1.c1.value('@transCode[1]' , 'VARCHAR(2)') as 'RDI_transCode',
T1.c1.value('@returnCode[1]' , 'VARCHAR(10)') as 'RDI_returnCode',
T1.c1.value('@returnDescription[1]' , 'VARCHAR(50)') as 'RDI_returnDescription',
T1.c1.value('@originalProcessingDate[1]' , 'VARCHAR(10)') as 'RDI_originalProcessingDate',
T1.c1.value('@valueOf[1]' , 'VARCHAR(13)') as 'RDI_valueOf',
T1.c1.value('@currency[1]' , 'VARCHAR(3)') as 'RDI_currency',
T1.c1.value('./PayerAccount/@number[1]' , 'VARCHAR(8)') as 'PA_number',
T1.c1.value('./PayerAccount/@ref[1]' , 'VARCHAR(18)') as 'PA_ref',
T1.c1.value('./PayerAccount/@name[1]' , 'VARCHAR(18)') as 'PA_name',
T1.c1.value('./PayerAccount/@sortCode[1]' , 'VARCHAR(8)') as 'PA_sortCode',
T1.c1.value('./PayerAccount/@bankName[1]' , 'VARCHAR(50)') as 'PA_bankName',
T1.c1.value('./PayerAccount/@branchName[1]' , 'VARCHAR(50)') as 'PA_branchName'
FROM @XMLVAR.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem') AS T1(c1)


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

Go to Top of Page

Pete_N
Posting Yak Master

156 Posts

Posted - 01/26/2013 :  04:23:27  Show Profile  Reply with Quote
HI,

when trying the code you suggested I get the following error
Msg 2389, Level 16, State 1, Line 33
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/26/2013 :  04:32:23  Show Profile  Reply with Quote
can you post your sample xml?

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

Go to Top of Page

Pete_N
Posting Yak Master

156 Posts

Posted - 01/26/2013 :  04:47:57  Show Profile  Reply with Quote
HI,

I have changed the data for security reasons

<?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-01-07"></Header>
<AddresseeInformation name="COMPANY NAME"></AddresseeInformation>
<ServiceLicenseInformation userName="USER NAME" userNumber="55555555"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="ME LIMITED" number="88888888" sortCode="11-22-33" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="123" transCode="17" returnCode="6003" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-01-03" valueOf="1.00" currency="GBP"><PayerAccount number="12345678" ref="123" name="M MOUSE" sortCode="00-00-00" bankName="NATIONWIDE BLDG SCTY" branchName="FlexAccount (Original)"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="321" transCode="17" returnCode="6003" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-01-03" valueOf="4.00" currency="GBP"><PayerAccount number="11111111" ref="321" name="J SMITH" sortCode="00-00-00" bankName="LLOYDS TSB BANK PLC" branchName="HORSHAM"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="456" transCode="17" returnCode="0003" returnDescription="REFER TO PAYER" originalProcessingDate="2013-01-03" valueOf="2.00" currency="GBP"><PayerAccount number="22222222" ref="456" name="J JONES" sortCode="99-99-99" bankName="LLOYDS TSB BANK PLC" branchName="LITTLEHAMPTON (309509)"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="999" transCode="17" returnCode="6003" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-01-03" valueOf="3.00" currency="GBP"><PayerAccount number="33333333" ref="999" name="PETER PAN" sortCode="66-66-66" bankName="HALIFAX" branchName="CROYDON NORTH END"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="4" valueOf="10.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data><SignatureMethod>Vanilla</SignatureMethod><Signature></Signature></BACSDocument>
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/26/2013 :  05:06:08  Show Profile  Reply with Quote

DECLARE @XMLVar xml

SET @XMLVar='<?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-01-07"></Header>
<AddresseeInformation name="COMPANY NAME"></AddresseeInformation>
<ServiceLicenseInformation userName="USER NAME" userNumber="55555555"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="ME LIMITED" number="88888888" sortCode="11-22-33" type="0" bankName="HSBC BANK PLC" branchName="ST ALBANS"></OriginatingAccount>
<ReturnedDebitItem ref="123" transCode="17" returnCode="6003" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-01-03" valueOf="1.00" currency="GBP"><PayerAccount number="12345678" ref="123" name="M MOUSE" sortCode="00-00-00" bankName="NATIONWIDE BLDG SCTY" branchName="FlexAccount (Original)"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="321" transCode="17" returnCode="6003" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-01-03" valueOf="4.00" currency="GBP"><PayerAccount number="11111111" ref="321" name="J SMITH" sortCode="00-00-00" bankName="LLOYDS TSB BANK PLC" branchName="HORSHAM"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="456" transCode="17" returnCode="0003" returnDescription="REFER TO PAYER" originalProcessingDate="2013-01-03" valueOf="2.00" currency="GBP"><PayerAccount number="22222222" ref="456" name="J JONES" sortCode="99-99-99" bankName="LLOYDS TSB BANK PLC" branchName="LITTLEHAMPTON (309509)"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="999" transCode="17" returnCode="6003" returnDescription="NO INSTRUCTION" originalProcessingDate="2013-01-03" valueOf="3.00" currency="GBP"><PayerAccount number="33333333" ref="999" name="PETER PAN" sortCode="66-66-66" bankName="HALIFAX" branchName="CROYDON NORTH END"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="4" valueOf="10.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data><SignatureMethod>Vanilla</SignatureMethod><Signature></Signature></BACSDocument>'

SELECT T1.c1.value('../@ref[1]' , 'VARCHAR(18)') as 'RDI_ref',
T1.c1.value('../@transCode[1]' , 'VARCHAR(2)') as 'RDI_transCode',
T1.c1.value('../@returnCode[1]' , 'VARCHAR(10)') as 'RDI_returnCode',
T1.c1.value('../@returnDescription[1]' , 'VARCHAR(50)') as 'RDI_returnDescription',
T1.c1.value('../@originalProcessingDate[1]' , 'VARCHAR(10)') as 'RDI_originalProcessingDate',
T1.c1.value('../@valueOf[1]' , 'VARCHAR(13)') as 'RDI_valueOf',
T1.c1.value('../@currency[1]' , 'VARCHAR(3)') as 'RDI_currency',
T1.c1.value('@number[1]' , 'VARCHAR(8)') as 'PA_number',
T1.c1.value('@ref[1]' , 'VARCHAR(18)') as 'PA_ref',
T1.c1.value('@name[1]' , 'VARCHAR(18)') as 'PA_name',
T1.c1.value('@sortCode[1]' , 'VARCHAR(8)') as 'PA_sortCode',
T1.c1.value('@bankName[1]' , 'VARCHAR(50)') as 'PA_bankName',
T1.c1.value('@branchName[1]' , 'VARCHAR(50)') as 'PA_branchName'
FROM @XMLVAR.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') AS T1(c1)


output
--------------------------------------------------------------------------------
RDI_ref	RDI_transCode	RDI_returnCode	RDI_returnDescription	RDI_originalProcessingDate	RDI_valueOf	RDI_currency	PA_number	PA_ref	PA_name	PA_sortCode	PA_bankName	PA_branchName
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123	17	6003	NO INSTRUCTION	2013-01-03	1.00	GBP	12345678	123	M MOUSE	00-00-00	NATIONWIDE BLDG SCTY	FlexAccount (Original)
321	17	6003	NO INSTRUCTION	2013-01-03	4.00	GBP	11111111	321	J SMITH	00-00-00	LLOYDS TSB BANK PLC	HORSHAM
456	17	0003	REFER TO PAYER	2013-01-03	2.00	GBP	22222222	456	J JONES	99-99-99	LLOYDS TSB BANK PLC	LITTLEHAMPTON (309509)
999	17	6003	NO INSTRUCTION	2013-01-03	3.00	GBP	33333333	999	PETER PAN	66-66-66	HALIFAX	CROYDON NORTH END


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


Edited by - visakh16 on 01/26/2013 05:06:42
Go to Top of Page

Pete_N
Posting Yak Master

156 Posts

Posted - 01/26/2013 :  05:08:36  Show Profile  Reply with Quote
Hi Vasakh

Brilliant thank you for your help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/26/2013 :  05:23:04  Show Profile  Reply with Quote
welcome

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