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.
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 itthe following section of code returns each record 4 times, instead of just 4 records I am getting a result with 16 recordsSELECT 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 thisSELECT 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 MVPhttp://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 errorMsg 2389, Level 16, State 1, Line 33XQuery [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 MVPhttp://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"?><!-- 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> |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-26 : 05:06:08
|
[code]DECLARE @XMLVar xmlSET @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 HORSHAM456 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 MVPhttp://visakhm.blogspot.com/ |
|
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2013-01-26 : 05:08:36
|
Hi VasakhBrilliant thank you for your help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-26 : 05:23:04
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|