代码之家 › 专栏 › 技术社区 › Ehsan Akbar

SQL Server xml查询未返回预期结果

qxmlquery xml-parsing xml sql-server

Ehsan Akbar · 技术社区 · 6 年前

我的数据库里有一列 FlowDetailParameter FlowDetail参数 以及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行)为什么?

1 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

相关子查询 . 外部没有到当前行的连接 SELECT 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