Menu
I need to query Xml data that has multiple REPORT tag elements. It needs to be filtered to return only the rows where the REPORTID is equal to a given name. I've tried to perform this filter with no luck. Could someone point me in the right direction here using the Sql Server Xml functions?
Basically, I'm looking for my result set to return as a table and look like:
ID
------
1
2
3
Given the following, how would I select out on the rows for the REPORT where the REPORTID (/TEST/REPORT/TITLE[@ReportId = "Report One"]) is 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>';
451
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
