mcorkers mcorkers - 2 months ago 14
SQL Question

Iterate through XML list when parsing SQL server

I have this script which selects a value from an xml report. The report contains many of these values of the same name and I would like to select all the values in the list.

I know the number of items I need to iterate through

;WITH XMLNAMESPACES (DEFAULT 'uri:website.co.uk/soap:examplenamespace')
select top 100 report.value('(//report/paymentinfo/paymentdate)[1]', 'nvarchar(200)'), report
from
[dbo].[reports]


There are 50 values i need to iterate through, not just the first 1 so something like

;WITH XMLNAMESPACES (DEFAULT 'uri:website.co.uk/soap:examplenamespace')
select top 100 report.value('(//report/paymentinfo/paymentdate)[1-50]', 'nvarchar(200)'), report
from
[dbo].[reports]

Answer

Thx for providing the XML, now the answer is easy:

CREATE TABLE #YourReportTable(report XML);
INSERT INTO #YourReportTable VALUES
('<report>
     <accs>
        <acc>
          <paymentinfo>
            <paymentdate>2001-03-05</paymentdate>
            <amount>1200.00</amount>
          </paymentinfo>
        </acc>
        <acc>
          <paymentinfo>
            <paymentdate>2001-04-05</paymentdate>
            <amount>1200.00</amount>
          </paymentinfo>
        </acc>
        <acc>
          <paymentinfo>
            <paymentdate>2001-05-05</paymentdate>
            <amount>1200.00</amount>
          </paymentinfo>
        </acc>
    </accs>
  </report>');

 SELECT TOP 100 rep.value('(paymentdate)[1]', 'date') AS PaymentDate
               ,rep.value('(amount)[1]', 'decimal(10,4)') AS Amount
 FROM #YourReportTable AS repTbl
 CROSS APPLY repTbl.report.nodes('/report/accs/acc//paymentinfo') AS A(rep);
 GO

 DROP TABLE #YourReportTable;

The result

PaymentDate Amount
2001-03-05  1200.0000
2001-04-05  1200.0000
2001-05-05  1200.0000
Comments