Xml Query With More Than One Condition

It is my sql data :

Solution 1:

You can apply search criteria to subnodes directly in your XQuery:

declare @t table (XData xml);

declare @StatusValue varchar(50) = 'Pending',
    @DepartmentId int = 23;

insert into @t (XData)
values (
N'<ArrayOfFlowDetailParametersxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema"><FlowDetailParameters><DepartmentId>23</DepartmentId><UserId  /><Username /><FullName /><ConfirmDateTime  /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters><FlowDetailParameters><DepartmentId>22</DepartmentId><UserId  /><Username /><FullName /><ConfirmDateTime  /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters><FlowDetailParameters><DepartmentId>7</DepartmentId><UserId  /><Username /><FullName /><ConfirmDateTime  /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters><FlowDetailParameters><DepartmentId>18</DepartmentId><UserId  /><Username /><FullName /><ConfirmDateTime  /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters></ArrayOfFlowDetailParameters>');

select a.c.query('.')
from @t t
    cross apply t.XData.nodes('/ArrayOfFlowDetailParameters/FlowDetailParameters[
        ./DepartmentId[1]/text() = sql:variable("@DepartmentId")
        and ./Status[1]/text() = sql:variable("@StatusValue")
    ]') a(c);

Also, since you will probably need this, I have also provided an example of how to parameterise such a query.

Solution 2:

If you want to pull out the values and the relevant XML part that apply your conditions you can try using this:

DECLARE @table table (XmlCol xml )
insert into @table
values 
('<ArrayOfFlowDetailParametersxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema"><FlowDetailParameters><DepartmentId>23</DepartmentId><UserIdxsi:nil="true" /><Username /><FullName /><ConfirmDateTimexsi:nil="true" /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters><FlowDetailParameters><DepartmentId>22</DepartmentId><UserIdxsi:nil="true" /><Username /><FullName /><ConfirmDateTimexsi:nil="true" /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters><FlowDetailParameters><DepartmentId>7</DepartmentId><UserIdxsi:nil="true" /><Username /><FullName /><ConfirmDateTimexsi:nil="true" /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters><FlowDetailParameters><DepartmentId>18</DepartmentId><UserIdxsi:nil="true" /><Username /><FullName /><ConfirmDateTimexsi:nil="true" /><Comment /><Status>Pending</Status><AttachmentId /></FlowDetailParameters></ArrayOfFlowDetailParameters>')

;
WITH XmlBreak as 
(
SELECT x.v.query('.') TheXml,
       x.v.value('(DepartmentId/text())[1]', 'int') DepartmentId,
       x.v.value('(Status/text())[1]', 'varchar(50)') Status    
 FROM @table t
    CROSS APPLY XmlCol.nodes ('/ArrayOfFlowDetailParameters/FlowDetailParameters') x(v)
)
SELECT * 
FROM XmlBreak
WHERE DepartmentId = 23 and Status = 'Pending'

Share