读取xml数据的格式:Sql server
读取xml数据的格式:Sql server
提问于 2018-03-27 17:23:46
我有如下的xml数据：
<GProposal>
<UnderwritingMessages>
<anyType xsi:type="xsd:string">3425：入院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
<anyType xsi:type="xsd:string">3428：通院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
</UnderwritingMessages>
<Plans>
<Plan>
<UnderwritingMessages />
<InsuredGroups>
<InsuredGroup>
<UnderwritingMessages>
<anyType xsi:type="xsd:string">342025：入院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
<anyType xsi:type="xsd:string">342028：通院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
</UnderwritingMessages>
<Description>役員・事業主</Description>
<InsuredGroupType>1</InsuredGroupType>
</InsuredGroup>
<InsuredGroup>
<UnderwritingMessages>
<anyType xsi:type="xsd:string">342025：入院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
<anyType xsi:type="xsd:string">342028：通院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
</UnderwritingMessages>
</InsuredGroup>
</InsuredGroups>
</Plan>
</Plans>
</GProposal>投保组可以有最大值4，计划可以有最大值3我需要如何使用循环获取。
我使用下面的格式来获取以下值。
select distinct P.proposalid as 'Proposal Id',
(CASE WHEN p2.XMLData.value('count(/Plans/Plan/InsuredGroups/InsuredGroup/UnderwritingMessages/*)', 'int') > 0 THEN
(SELECT TOP 1 T.c.value('.', 'nvarchar(max)') AS result
FROM p2.XMLData.nodes('(/*/Plans/Plan/InsuredGroups/InsuredGroup/UnderwritingMessages)') T(c))
ELSE
(SELECT TOP 1 T.c.value('.', 'nvarchar(max)') AS result
FROM p2.XMLData.nodes('(/*/UnderwritingMessages)') T(c))
END) as UWReferralComment
from proposal P
-- referred before
cross APPLY (
SELECT TOP 1 ProposalId , Starteffectivedate,Data as XMLData
FROM Proposal
WHERE ProposalId = P.ProposalId
AND ProposalTypeID = 1
AND ProposalStatusID = 3
AND EndEffectiveDate <> '21991231'
--and len(isnull(Data.value('(/*/Answers/AnswersList/Entry[@key="uc7_UWApprovalNum"]/value)[1]', 'nvarchar(max)'),'') ) > 0
) P2输出：
我需要组合所有的UnderwritingMessages标签，以显示为没有重复的字符串。
回答 1
Stack Overflow用户
发布于 2018-03-27 18:46:21
你的问题很不清楚...提供的XML不是格式良好的(缺少xsi命名空间)，您自己的SELECT似乎过于复杂，我看不出您的子选择的原因，我也不能遵循您的XQuery，它提到了一个在提供的XML中不可见的路径。
您可以尝试以下操作：
模拟您的访问的模拟表格：
DECLARE @proposal TABLE(ID INT IDENTITY,XmlData XML);
INSERT INTO @proposal VALUES
(N'<GProposal xmlns:xsi="dummy">
<UnderwritingMessages>
<anyType xsi:type="xsd:string">3425：入院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
<anyType xsi:type="xsd:string">3428：通院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
</UnderwritingMessages>
<Plans>
<Plan>
<UnderwritingMessages />
<InsuredGroups>
<InsuredGroup>
<UnderwritingMessages>
<anyType xsi:type="xsd:string">342025：入院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
<anyType xsi:type="xsd:string">342028：通院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
</UnderwritingMessages>
<Description>役員・事業主</Description>
<InsuredGroupType>1</InsuredGroupType>
</InsuredGroup>
<InsuredGroup>
<UnderwritingMessages>
<anyType xsi:type="xsd:string">342025：入院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
<anyType xsi:type="xsd:string">342028：通院補償一時金が引受限度額を超えています。引受照会が必要です。</anyType>
</UnderwritingMessages>
</InsuredGroup>
</InsuredGroups>
</Plan>
</Plans>
</GProposal>');--这个查询将使用分层嵌套的.nodes()调用为您的XML中的任何值提供一个模板。我假设<Plans><Plan>既指向1:n层次结构也指向<InsuredGroups><InsuredGroup>层次结构。此外，它看起来好像任何级别都可以有<UnderwritingMessages>
SELECT TopMessages.value(N'text()[1]','nvarchar(max)') AS TopMessage
,B.pl.value(N'(SomeValueHere)[1]',N'nvarchar(max)') AS SomeValueOnPlanLevel
,B1.at.value(N'text()[1]',N'nvarchar(max)') AS MessageOnPlanLevel
,C.ig.value(N'(SomeValueHere)[1]',N'nvarchar(max)') AS SomeValueOnGroupLevel
,C1.at.value(N'text()[1]',N'nvarchar(max)') AS MessageOnGroupLevel
FROM @proposal AS p
OUTER APPLY p.XmlData.nodes(N'/GProposal/UnderwritingMessages/anyType') AS A(TopMessages)
OUTER APPLY p.XmlData.nodes(N'/GProposal/Plans/Plan') AS B(pl)
OUTER APPLY pl.nodes(N'UnderwritingMessages/anyType') AS B1(at)
OUTER APPLY pl.nodes(N'InsuredGroups/InsuredGroup') AS C(ig)
OUTER APPLY ig.nodes(N'UnderwritingMessages/anyType') AS C1(at);但如果我从字面上理解你的问题：
我需要组合所有的UnderwritingMessages标签，以显示为没有重复的字符串。
...this可能就足够了：
SELECT msg.value(N'text()[1]',N'nvarchar(max)') AS UnderwritingMessage
,msg.value(N'local-name(../..)',N'nvarchar(max)') AS Location
FROM @proposal AS p
OUTER APPLY p.XmlData.nodes(N'//anyType') AS A(msg);您可以使用DISTINCT或GROUP BY来获得不重复的结果。
更新
在查阅了我的魔术水晶球之后，我得到的印象是，您正在寻找一种称为字符串聚合的东西。Version 2016+提供了STRING_AGG，但对于您的版本，您需要执行以下操作：
SELECT ID
,STUFF((
SELECT DISTINCT ', ' + msg.value(N'text()[1]',N'nvarchar(max)')
FROM p.XmlData.nodes(N'//UnderwritingMessages/anyType') AS A(msg)
FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,2,'') AS UWReferralComment
FROM @proposal AS p;ID表示表中的任何“普通”列，请添加所需的列。子选择将读取xml中的所有消息，并将它们连接在一个由逗号分隔的大字符串中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49509028
复制相关文章
相似问题
腾讯云开发者
