XML-запрос SQL Server не возвращает ожидаемый результат

У меня есть столбец в моей базе данных FlowDetailParameter с типом XML. Моя таблица имеет один столбец FlowDetailParameter и 3 строки с этими данными:

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>

Я хочу найти departmentId=3 and status=Pending, поэтому ожидаемый результат должен возвращать 2 строки. Итак, вот мой запрос:

    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)

Но мой запрос возвращает все строки (3 строки), почему?


xml sql-server xml-parsing qxmlquery
person Ehsan Akbar    schedule 01.11.2018    source источник
comment
Есть ли в вашей таблице другие столбцы? Например, первичный ключ или идентификатор, который идентифицирует каждую строку?   -  person Andrea    schedule 02.11.2018
comment
@andrea да, в моей таблице есть другие столбцы, такие как статус идентификатора   -  person Ehsan Akbar    schedule 02.11.2018

Ответы (1)


arrow_upward
1
arrow_downward

Сначала ответ на ваш вопрос «почему?»:

Ваш подзапрос не является коррелированным подзапросом. Нет связи с текущей строкой из внешнего SELECT. Итак, если есть хотя бы 1 строка, удовлетворяющая вашему условию, это всегда будет count>0.

Хотя ваш подход можно исправить, я бы предложил использовать XML-метод .exist() и предоставить фильтр как XPath/XQuery:

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

Это проверит наличие любого <FlowDetailParameters> для данного условия.

Если вы хотите ввести фильтр динамически, вы можете использовать sql:variable() или sql:column() вместо 3 и "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
person Shnugo    schedule 03.11.2018
comment
Спасибо дорогой друг за отличный и полный ответ - person Ehsan Akbar; 03.11.2018