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

具有多个条件的xml查询

sqlxml xml sql-server

Ehsan Akbar · 技术社区 · 7 年前

这是我的sql数据:

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

FlowDetailParameters 有两个条件意味着 departmentid=23 and status=pending .

我的问题是:

SELECT
    Requests.* 
FROM
    Requests
WHERE
    (@organizationid IS NULL OR OrganizationId = @organizationid) 
    AND (@DetailStatus IS NULL 
         OR (EXISTS (SELECT *
                     FROM Requests.FlowDetailParameter.nodes('/ArrayOfFlowDetailParameters/FlowDetailParameters/DepartmentId') as Parms(DepartmentId)
                     WHERE DepartmentId.value('.', 'bigint') = @departmentId) 
         AND EXISTS (SELECT *
                     FROM Requests.FlowDetailParameter.nodes('/ArrayOfFlowDetailParameters/FlowDetailParameters/Status') as Parms(Status)
                     WHERE Status.value('.', 'nvarchar(max)') = @DetailStatus)))

但是我的查询会找到 departmentid=23 or status=pending

2 回复 | 直到 7 年前

Roger Wolf 7 年前

您可以直接在XQuery中将搜索条件应用于子节点:

declare @t table (XData xml);

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

insert into @t (XData)
values (
N'<ArrayOfFlowDetailParameters xmlns: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);

另外,由于您可能需要这样做,我还提供了一个如何参数化这样一个查询的示例。

hkravitz 7 年前

如果要提取应用条件的值和相关XML部分,可以尝试使用以下方法:

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