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-10-04 : 08:29:37
|
| I am reading data from an xml field ,in the xml there are 4 records, and with the code below I get what i need. I am however having trouble trying to get the info from <actionTaken> everything I have tried results in a return of 16 recordsSample XML <ReturnedDebitItem ref="MY REF" transCode="17" returnCode="0004" returnDescription="REFER TO PAYER" originalProcessingDate="2013-01-04" valueOf="0.01" currency="GBP"> <PayerAccount number="01010101" ref="MY REF" name="MY NAME" sortCode="11-00-11" bankName="BANK PLC" branchName="TOWN HALL SQUARE" /> <ActionTaken action="No Action taken" /></ReturnedDebitItem>TSQL SELECT w.c.value('@ref[1]' , 'VARCHAR(18)') AS RTNref, w.c.value('@currency[1]' , 'VARCHAR(4)') AS RTNcurrency, CAST(w.c.value('../@valueOf[1]' , 'VARCHAR(13)')AS DECIMAL(11,2))* -1 AS RTNvalueOf, w.c.value('@name[1]' , 'VARCHAR(18)') AS RTNname, w.c.value('@sortCode[1]' , 'VARCHAR(8)') AS RTNsortCode, w.c.value('@number[1]' , 'VARCHAR(8)') AS RTNnumber , w.c.value('../@transCode[1]' , 'VARCHAR(2)') AS RTtransCode , w.c.value('../@returnCode[1]' , 'VARCHAR(4)') AS RTNreturnCode, w.c.value('../@returnDescription[1]' , 'VARCHAR(50)') AS RTNreturnDescription, CONVERT( SMALLDATETIME,w.c.value('../@originalProcessingDate[1]' , 'VARCHAR(10)')) AS RTNoriginalProcessingDate FROM (SELECT CAST([new_xmlfile] as XML) AS Xmlreport FROM [XXX].[XXXXMSCRM].[dbo].[XXXXXXXX] WHERE new_aruddreportsId = @Id) d CROSS APPLY Xmlreport.nodes('//Data/ARUDD') AS a(c) OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem/PayerAccount') w(c) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-04 : 08:48:22
|
try likeSELECT w.c.value('(./PayerAccount/@ref)[1]' , 'VARCHAR(18)') AS RTNref,w.c.value('@currency[1]' , 'VARCHAR(4)') AS RTNcurrency,CAST(w.c.value('@valueOf[1]' , 'VARCHAR(13)')AS DECIMAL(11,2))* -1 AS RTNvalueOf,w.c.value('(./PayerAccount/@name)[1]' , 'VARCHAR(18)') AS RTNname,w.c.value('(./PayerAccount/@sortCode)[1]' , 'VARCHAR(8)') AS RTNsortCode, w.c.value('(./PayerAccount/@number)[1]' , 'VARCHAR(8)') AS RTNnumber ,w.c.value('@transCode[1]' , 'VARCHAR(2)') AS RTtransCode ,w.c.value('@returnCode[1]' , 'VARCHAR(4)') AS RTNreturnCode,w.c.value('@returnDescription[1]' , 'VARCHAR(50)') AS RTNreturnDescription,CONVERT( SMALLDATETIME,w.c.value('@originalProcessingDate[1]' , 'VARCHAR(10)')) AS RTNoriginalProcessingDate,w.c.value('(./ActionTaken/@action)[1]','varchar(100)') AS actionTakenFROM(SELECT CAST([new_xmlfile] as XML) AS Xmlreport FROM [XXX].[XXXXMSCRM].[dbo].[XXXXXXXX] WHERE new_aruddreportsId = @Id) dCROSS APPLY Xmlreport.nodes('//Data/ARUDD') AS a(c)OUTER APPLY a.c.nodes('Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem') w(c)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2013-10-04 : 09:00:44
|
| Hi visakh16,Thank you for that. I had tried something similar, but your example has shown me where I went wrongBrilliant! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-04 : 10:17:35
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Reading XML