SQL Server xml query doesn't return expected result

Question

I have a column in my database FlowDetailParameter with XML Type .My table has one column FlowDetailParameter and 3 rows with these data :

row 1

<ArrayOfFlowDetailParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <FlowDetailParameters>
    <DepartmentId>7</DepartmentId>
    <UserId>6</UserId>
    <Username>4</Username>
    <FullName>کارشناس  معاینه فنی</FullName>
    <ConfirmDateTime>2018-11-01T10:45:29.7371421+03:30</ConfirmDateTime>
    <Comment>اولین IP تاییدی</Comment>
    <Status>Accept</Status>
  </FlowDetailParameters>
  <FlowDetailParameters>
    <DepartmentId>3</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
</ArrayOfFlowDetailParameters>

row 2

<ArrayOfFlowDetailParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <FlowDetailParameters>
    <DepartmentId>7</DepartmentId>
    <UserId>6</UserId>
    <Username>4</Username>
    <FullName>کارشناس  معاینه فنی</FullName>
    <ConfirmDateTime>2018-11-01T10:45:40.437481+03:30</ConfirmDateTime>
    <Comment>دومین IP تاییدی</Comment>
    <Status>Accept</Status>
  </FlowDetailParameters>
  <FlowDetailParameters>
    <DepartmentId>3</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
</ArrayOfFlowDetailParameters>


row 3

<ArrayOfFlowDetailParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <FlowDetailParameters>
    <DepartmentId>7</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status>Pending</Status>
    <AttachmentId />
  </FlowDetailParameters>
  <FlowDetailParameters>
    <DepartmentId>3</DepartmentId>
    <UserId xsi:nil="true" />
    <Username />
    <FullName />
    <ConfirmDateTime xsi:nil="true" />
    <Comment />
    <Status />
    <AttachmentId />
  </FlowDetailParameters>
</ArrayOfFlowDetailParameters>

I want to find the departmentId=3 and status=Pending ,so the expected result should return 2 rows .So here is my query :

    select  Requests.*   from Requests

 where    
  ((SELECT count(*)   
 FROM Requests t
    CROSS APPLY t.FlowDetailParameter.nodes ('/ArrayOfFlowDetailParameters/FlowDetailParameters') x(v)
where    x.v.value('(DepartmentId/text())[1]', 'bigint')=3   and  x.v.value('(Status/text())[1]', 'varchar(50)') = 'Pending') >0)

But my query returns all rows (3 rows) why ?

Are there any other columns in your table? For example a primary key or an id that identifies each row? — Andrea
– Andrea, Commented Nov 2, 2018 at 8:32
@andrea yes there is another columns like Id status in my table — Ehsan Akbar
– Ehsan Akbar, Commented Nov 2, 2018 at 14:23

Gottfried Lesigang · Accepted Answer · 2018-11-03 16:08:59Z

1

First to answer your question "why?":

Your sub-query is not a correlated sub-query. There is no connection to the current row from the outer SELECT. So - assuming there is at least 1 row fulfilling your condition - this will always provide a count>0.

Although your approach can be corrected, I'd suggest to use the XML-method .exist() and provide the filter as XPath/XQuery:

SELECT * 
FROM Requests r
WHERE r.FlowDetailParameter.exist(N'/ArrayOfFlowDetailParameters
                                    /FlowDetailParameters[(DepartmentId/text())[1]=3 
                                                          and (Status/text())[1]="Pending"]')=1;

This will check for the existance of any <FlowDetailParameters> for the given condition.

If you want to introduce the filter dynamically, you can use sql:variable() or sql:column() instead of 3 and "Pending"

DECLARE @depId INT=3;
DECLARE @status VARCHAR(100)='Pending';

SELECT * 
FROM Requests r
WHERE r.FlowDetailParameter.exist(N'/ArrayOfFlowDetailParameters
                                    /FlowDetailParameters[(DepartmentId/text())[1]=sql:variable("@depId")
                                                          and (Status/text())[1]=sql:variable("@status")]')=1

answered Nov 3, 2018 at 16:08

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ehsan Akbar Over a year ago

Thank you dear friend for great and complete answer

Collectives™ on Stack Overflow

SQL Server xml query doesn't return expected result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related