Geek asks and answers

Sql Server Xml for table logic

I need to request Xml data with several elements of the REPORT tag. It must be filtered to return only rows in which REPORTID is equal to the specified name. I tried to execute this filter with no luck. Can someone point me in the right direction here using the Sql Server Xml features?

Basically, I am looking for my result set to return as a table and look like this:

ID
------
1
2
3

Given the following, how would I select the rows for the REPORTTID report (/ TEST / REPORT / TITLE [@ReportId = "Report One"]) equal to "Report One"?

DECLARE @Xml XML, @ReportId VARCHAR(200);
SET @ReportId = 'Report One';

SET @Xml = '
<TEST>
<REPORT ReportType="Type One">
  <TITLE ReportId="Report One">
    <TITLE1>Title One</TITLE1>
  </TITLE>
  <HEADER>
    <Run_Date OrigName="Run Date">4/10/2012</Run_Date>
  </HEADER>
  <BODY>
    <TABLE1>
      <DATA />
      <ROW>
        <ID>1</ID>
      </ROW>
      <ROW>
        <ID>2</ID>
      </ROW>
      <ROW>
        <ID>3</ID>
      </ROW>
    </TABLE1>
  </BODY>
</REPORT>
<REPORT ReportType="Type Two">
  <TITLE ReportId="Report Two">
    <TITLE1>Title Two</TITLE1>
  </TITLE>
  <HEADER>
    <Run_Date OrigName="Run Date">4/10/2012</Run_Date>
  </HEADER>
  <BODY>
    <TABLE1>
      <DATA />
      <ROW>
        <ID>4</ID>
      </ROW>
      <ROW>
        <ID>5</ID>
      </ROW>
      <ROW>
        <ID>6</ID>
      </ROW>
    </TABLE1>
  </BODY>
</REPORT>
</TEST>';
+3
source share
2 answers
select I.N.value('.', 'int') as ID 
from @Xml.nodes('TEST/REPORT') as R(N)
  cross apply R.N.nodes('BODY/TABLE1/ROW/ID') as I(N)
where R.N.exist('TITLE[@ReportId = sql:variable("@ReportId")]') = 1
+2
source

, , . CROSS APPLY , XPATH ().

SELECT  ID = c.value('.', 'int')
FROM @Xml.nodes('/TEST/REPORT[TITLE/@ReportId=sql:variable("@ReportId")]/BODY/TABLE1/ROW/ID') x(c)
0

More articles:


All Articles