records x 4 - 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)

records x 4

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-01-25 : 20:01:59

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

52326 Posts

Posted - 2013-01-26 : 00:08:00

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/

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-01-26 : 04:23:27

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 04:32:23

can you post your sample xml?

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-01-26 : 04:47:57

HI,

I have changed the data for security reasons

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 05:06:08

[code]
DECLARE @XMLVar xml

SET @XMLVar='<?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-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

[/code]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-01-26 : 05:08:36

Hi Vasakh

Brilliant thank you for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 05:23:04

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources