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)

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