sql-server - 如何查询父重复节点内的重复 XML 子节点?
问题描述
我将以下 XML 存储在 nText 列中(不是我的设计,旧数据库)。我需要提取作为 Coverage 子节点属性的 PolicyNumber 和 CvgCode。
<efs:Request
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:efs="http://www.slsot.org/efs"
xsi:schemaLocation="http://www.slsot.org/efs
http://efs.slsot.org/efs/xsd/SlsotEfsSchema2.xsd">
<EfsVersion>2.0</EfsVersion>
<Batch BatchType="N" AgLicNo="12345" ItemCnt="69">
<EFSPolicy>
<PolicyNumber>POL12345</PolicyNumber>
<Binder>0086592YZ</Binder>
<TransType>N</TransType>
<Insured>Dummy Co LLC</Insured>
<ZipCode>75225</ZipCode>
<ClassCd>99930</ClassCd>
<PolicyFee>35.00</PolicyFee>
<TotalTax>36.62</TotalTax>
<TotalStampFee>1.13</TotalStampFee>
<TotalGross>792.75</TotalGross>
<EffectiveDate>09/17/2018</EffectiveDate>
<ExpirationDate>09/17/2019</ExpirationDate>
<IssueDate>09/20/2018</IssueDate>
<ContUntilCancl>N</ContUntilCancl>
<FedCrUnion>N</FedCrUnion>
<AORFlag>N</AORFlag>
<CustomID>043684</CustomID>
<WindStormExclusion>N</WindStormExclusion>
<CorrectionReEntry>N</CorrectionReEntry>
<Coverages>
<Coverage CvgCode="9325">720.00</Coverage>
</Coverages>
<Securities>
<Company CoNumber="80101168">100.00</Company>
</Securities>
</EFSPolicy>
<EFSPolicy>
...
</EFSPolicy>
</Batch>
</efs:Request>
这是我用来提取 PolicyNumber 的 SQL 代码(到目前为止)。
with cte_table(BatchID, xmlData)
AS
(SELECT BatchID, CAST(CAST(xmlData AS VARCHAR(MAX)) AS XML) from
Batches)
select
s.BatchID
,t.c.value('PolicyNumber[1]', 'varchar(max)') as PolicyNumber
from cte_table as s
cross apply s.xmlData.nodes('/*:Request/Batch/EFSPolicy') as t(c)
where BatchID in (select batchID from Batches where CreateDate between '1/1/19' and getdate())
我在 Coverages 节点上尝试了第二个 CROSS APPLY,但这给了我每个批次的所有 Coverage 值(不是 CvgCode 属性),所以我的结果集是行数的 100+ 倍。我认为这是由于第二次 CROSS APPLY,是否有 INNER JOIN 类型的 CROSS APPLY?
解决方案
您需要声明命名空间以检索数据:
DECLARE @XML xml = '<efs:Request
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:efs="http://www.slsot.org/efs"
xsi:schemaLocation="http://www.slsot.org/efs
http://efs.slsot.org/efs/xsd/SlsotEfsSchema2.xsd">
<EfsVersion>2.0</EfsVersion>
<Batch BatchType="N" AgLicNo="12345" ItemCnt="69">
<EFSPolicy>
<PolicyNumber>POL12345</PolicyNumber>
<Binder>0086592YZ</Binder>
<TransType>N</TransType>
<Insured>Dummy Co LLC</Insured>
<ZipCode>75225</ZipCode>
<ClassCd>99930</ClassCd>
<PolicyFee>35.00</PolicyFee>
<TotalTax>36.62</TotalTax>
<TotalStampFee>1.13</TotalStampFee>
<TotalGross>792.75</TotalGross>
<EffectiveDate>09/17/2018</EffectiveDate>
<ExpirationDate>09/17/2019</ExpirationDate>
<IssueDate>09/20/2018</IssueDate>
<ContUntilCancl>N</ContUntilCancl>
<FedCrUnion>N</FedCrUnion>
<AORFlag>N</AORFlag>
<CustomID>043684</CustomID>
<WindStormExclusion>N</WindStormExclusion>
<CorrectionReEntry>N</CorrectionReEntry>
<Coverages>
<Coverage CvgCode="9325">720.00</Coverage>
</Coverages>
<Securities>
<Company CoNumber="80101168">100.00</Company>
</Securities>
</EFSPolicy>
<EFSPolicy>
</EFSPolicy>
</Batch>
</efs:Request>';
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as xsi,
'http://www.slsot.org/efs' AS efs)
SELECT EFS.[Policy].value('(./PolicyNumber/text())[1]','varchar(25)') AS PolicyNumber,
EFS.[Policy].value('(./Coverages/Coverage/@CvgCode)[1]','int') AS CvgCode --Assumes only 1 CvgCode per policy
FROM (VALUES(@XML)) V(X)
CROSS APPLY V.X.nodes('efs:Request/Batch/EFSPolicy') EFS([Policy]);
推荐阅读
- vb.net - 自动生成 ID 号 VB.Net 不工作 [Access DB]
- python - Python 中的因果影响分析 - P 值似乎不正确
- javascript - 在对象数组中按公共属性分组项目
- c# - 为什么在索引时转换为动态列表会失败?
- css - CSS动画一个圆锥渐变作为边框图像
- django - 获取 ids 列表的最后一条记录 Django
- java - 爪哇 | 运行客户端时出现 MinecraftForge 错误
- node.js - 我应该在哪里放置中间件 nodejs
- arrays - Python代码查找其总和与特定值匹配的最小行数
- c# - 如何将 Postgresql 数据插入字符串数组