sql - 在 XML 中使用 2 CROSS APPLY
问题描述
我正在使用下面的 XML 来获取,ClauseCode
但它没有为下面的查询返回任何数据。
<Policy>
<Plans>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>3</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>1</ClauseCode>
<ClauseCode>2</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>5</ClauseCode>
<ClauseCode>4</ClauseCode>
</ProductEndorsementClauses>
</Plan>
</Plans>
</Policy>
这是我的查询:
select proposaid,
,Col1.value('(/*/ProductEndorsementClauses/ClauseCode)[1]','nvarchar(max)')
from Policy p
CROSS APPLY data.nodes('/*/Plans/Plan') AS Tbl(Col)
CROSS APPLY Tbl.Col.nodes('/ProductEndorsementClauses/ClauseCode') AS TblPec(Col1)
where Col1.value('(ProductEndorsementClauses/ClauseCode)[1]', 'nvarchar(max)') in ('1','3')
解决方案
灵感:
declare @x xml = N'
<Policy>
<Plans>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>3</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>1</ClauseCode>
<ClauseCode>2</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>5</ClauseCode>
<ClauseCode>4</ClauseCode>
</ProductEndorsementClauses>
</Plan>
</Plans>
</Policy>'
select @x
--all clause code values
select t.col.value('.[1]', 'nvarchar(10)')
from @x.nodes('Policy/Plans/Plan/ProductEndorsementClauses/ClauseCode') as t(col);
--only clause code values that have at least a 1, 3 clause code in their parent ProductEndorsementClauses
select t.col.value('.[1]', 'nvarchar(10)')
from @x.nodes('Policy/Plans/Plan/ProductEndorsementClauses[(./ClauseCode/text() = "1") or (./ClauseCode/text() = "3")]/ClauseCode') as t(col);
--result only when exists at least a 1 or 3
select t.col.value('.[1]', 'nvarchar(max)')
from @x.nodes('Policy/Plans/Plan/ProductEndorsementClauses/ClauseCode') as t(col)
where @x.exist('Policy/Plans/Plan/ProductEndorsementClauses/ClauseCode[text() = "1" or text() = "3"]') = 1;
declare @Policy table
(
proposaid int,
data xml
);
insert into @Policy(proposaid, data)
values(1, N'
<Policy>
<Plans>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>3</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>1</ClauseCode>
<ClauseCode>2</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>5</ClauseCode>
<ClauseCode>4</ClauseCode>
</ProductEndorsementClauses>
</Plan>
</Plans>
</Policy>'),
(2, N'
<Policy>
<Plans>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>13</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>11</ClauseCode>
<ClauseCode>12</ClauseCode>
</ProductEndorsementClauses>
</Plan>
<Plan>
<ProductEndorsementClauses>
<ClauseCode>15</ClauseCode>
<ClauseCode>14</ClauseCode>
</ProductEndorsementClauses>
</Plan>
</Plans>
</Policy>');
select *
from @Policy;
select p.proposaid,
t.col.value('.[1]', 'nvarchar(max)')
from @Policy as p
cross apply p.data.nodes('Policy/Plans/Plan/ProductEndorsementClauses/ClauseCode') as t(col)
where p.data.exist('Policy/Plans/Plan/ProductEndorsementClauses/ClauseCode[text() = "1" or text() = "3"]') = 1;
推荐阅读
- tensorflow2.0 - 为什么我们需要在 tensorflow-lite 中调用 MicroInterpreter 之前为特定模型定义 MicroMutableOpResolver 或 AllOpsResolver?
- sql - 如何编写返回一年第二季度的查询.....示例从 2011 年到 2016 年第二季度的总利润
- javascript - Django:如何根据表单中的选定选项在模板上显示来自 django 数据库的图像?
- go - 是否可以使用这样的代理?
- r - column mean for subset of rows in R data.table
- flutter - 无法使用 FutureBuilder 启动相机
- python - 违反约束的膳食计划优化(Python、OR-Tools、线性规划、XLRD)
- oracle - 服务器端的 Oracle 转移数据库
- r - 构造 2*2*2 列联表时遇到问题
- java - “mvn test”没有执行@Mock注解的方法