sql-server - 具有多个条件的 xml 查询
问题描述
这是我的 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
2 个条件,这意味着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
解决方案
如果您想提取应用您的条件的值和相关 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'
推荐阅读
- sql-server - TSQL:表类型未作为临时表处理?
- c - 交换字符串并检查C中的不相等条件?
- ruby-on-rails - 获取所有供应商的列表?
- python - BeautifulSoup 不能在 Python 的多线程程序中工作
- python - 获取两个日期之间的[年月日]差异
- vba - 如何选择 Sharepoint Online 上的“在文件资源管理器中查看”按钮,或者至少使用 VBA 运行它的操作?
- python - 截屏时 autopy 给出 ValueError
- c# - 我在cshtml中更改了id的名称,现在我将如何使用begin form将它传递给控制器
- r - 分组条形图,每个条形的堆积百分比
- graphics - D3DFMT_A32R32G32B32F 是线性还是 sRGB?