Prev: Need to recover my database
Next: SQL Server security design question - theory
From: Peter Newman on 4 Aug 2010 06:58
SQL 2008

I have a table with a XML field that stores an XML file.

I have no problems grabbing data from this field using the query below.
DECLARE @x xml;
Set @x = (Select ReportFile FROM XMLREports where LedgerKey = @LedgerRef);

BEGIN
SELECT
T5.c1.value('@ref[1]', 'varchar(max)') AS [RDI_name] ,
T5.c1.value('@transCode[1]', 'varchar(max)') AS [RDI_number],
T5.c1.value('@returnCode[1]','varchar(max)') AS [RDI_sortCode],
T5.c1.value('@returnDescription[1]', 'varchar(max)') AS [RDI_type],
T5.c1.value('@originalProcessingDate[1]', 'varchar(max)') AS [RDI_bankName],
T5.c1.value('@valueOf[1]', 'varchar(max)') AS [RDI_branchName],
T5.c1.value('@currency[1]', 'varchar(max)') AS [RDI_name]
FROM

@x.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem') As T5(c1)
END


BEGIN
SELECT
T6.c1.value('@number[1]', 'varchar(max)') AS [PA_number],
T6.c1.value('@ref[1]','varchar(max)') AS [PA_sortCode],
T6.c1.value('@name[1]', 'varchar(max)') AS [PA_name],
T6.c1.value('@sortCode[1]', 'varchar(max)') AS [PA_sortCode],
T6.c1.value('@bankName[1]', 'varchar(max)') AS [PA_bankName],
T6.c1.value('@branchName[1]', 'varchar(max)') AS [PA_branchName]
FROM

@x.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') As T6(c1)
END

This retruns me two result sets. There are 14 records in the XML and both
result sets return 14 records .. thats ok, however, I want to merge the two
queries into 1 and produce a result set as below. I have tried several
things but always come up with 196 records !! instead of 14

Required Result ( if Possible )

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
122222222222 17 0203 REFER TO
PAYER 2010-07-22 11.11 GBP 12345678 122222222222 MR SMITH
11-11-11 ANY BANK PLC ANYTOWN


I can supply the table def and sample XML data, but due to the senseative
nature of this file , it will take a while to generate a dummy one
From: Plamen Ratchev on 4 Aug 2010 11:09
Try this:

SELECT
T5.c1.value('../@ref[1]', 'varchar(max)') AS [RDI_name] ,
T5.c1.value('../@transCode[1]', 'varchar(max)') AS [RDI_number],
T5.c1.value('../@returnCode[1]','varchar(max)') AS [RDI_sortCode],
T5.c1.value('../@returnDescription[1]', 'varchar(max)') AS
[RDI_type],
T5.c1.value('../@originalProcessingDate[1]', 'varchar(max)') AS
[RDI_bankName],
T5.c1.value('../@valueOf[1]', 'varchar(max)') AS [RDI_branchName],
T5.c1.value('../@currency[1]', 'varchar(max)') AS [RDI_name],
T5.c1.value('@number[1]', 'varchar(max)') AS [PA_number],
T5.c1.value('@ref[1]','varchar(max)') AS [PA_sortCode],
T5.c1.value('@name[1]', 'varchar(max)') AS [PA_name],
T5.c1.value('@sortCode[1]', 'varchar(max)') AS [PA_sortCode],
T5.c1.value('@bankName[1]', 'varchar(max)') AS [PA_bankName],
T5.c1.value('@branchName[1]', 'varchar(max)') AS [PA_branchName]
FROM
@x.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/
OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') AS T5(c1);

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Need to recover my database
Next: SQL Server security design question - theory