sql - 如何将重复的标签/字符串数据插入表中
问题描述
嗨,我面临从复杂字符串中读取数据的问题。
我必须在字符串之后读取数据<deviMessage>
并插入表中。读取数据的问题<deviMessage>
是重复标签,我必须读取所有标签<deviMessage>
并插入表中。
<loan><applicant><partyReference>string</partyReference><
deviations><deviMessage>read this data</deviMessage><deviSeverity>L2</deviSeverity><approvalAuth>CCM</approvalAuth><istest>NO</istest></deviations>
deviations><deviMessage>read thid data2</deviMessage><deviSeverity>L2</deviSeverity><approvalAuth>CCM</approvalAuth><istest>NO</istest></deviations>
<Numerator>0.036848584684376046</Numerator></loan>
解决方案
此 XML 只是一个节点<loan>content</loan>
,其中内容是嵌入的 XML。
如果你从 XML 中读取它,它看起来像这样:
注意:我没有纠正一些错误!
<applicant>
<partyReference>string</partyReference>
<deviations>
<deviMessage>read this data</deviMessage>
<deviSeverity>L2</deviSeverity>
<approvalAuth>CCM</approvalAuth>
<istest>NO</istest>
</deviations>
deviations>
<deviMessage>read thid data2</deviMessage>
<deviSeverity>L2</deviSeverity>
<approvalAuth>CCM</approvalAuth>
<istest>NO</istest>
</deviations>
<Numerator>0.036848584684376046</Numerator>
<
在第二个之前有一个丢失,<deviations>
并且关闭</applicant>
也丢失了......
如果这是您的真实数据,那么您就处于一团糟......
修复输入中的这些错误后(希望您嵌入的 XML 是有效的 XML)。你可以这样做:
DECLARE @YourXML XML='<loan><applicant><partyReference>string</partyReference><deviations><deviMessage>read this data</deviMessage><deviSeverity>L2</deviSeverity><approvalAuth>CCM</approvalAuth><istest>NO</istest></deviations><deviations><deviMessage>read thid data2</deviMessage><deviSeverity>L2</deviSeverity><approvalAuth>CCM</approvalAuth><istest>NO</istest></deviations><Numerator>0.036848584684376046</Numerator></applicant></loan>';
SELECT CAST(@YourXML.value('.','nvarchar(max)') AS XML);
对于这个修复的 XML,您可以通过以下查询获取数据:
DECLARE @YourXML XML='<loan><applicant><partyReference>string</partyReference><deviations><deviMessage>read this data</deviMessage><deviSeverity>L2</deviSeverity><approvalAuth>CCM</approvalAuth><istest>NO</istest></deviations><deviations><deviMessage>read thid data2</deviMessage><deviSeverity>L2</deviSeverity><approvalAuth>CCM</approvalAuth><istest>NO</istest></deviations><Numerator>0.036848584684376046</Numerator></applicant></loan>';
SELECT TheXml.value('(/applicant/partyReference/text())[1]','nvarchar(150)') AS PartyReference
,devs.value('(deviMessage/text())[1]','nvarchar(150)') AS deviMessage
,devs.value('(deviSeverity/text())[1]','nvarchar(150)') AS deviSeverity
,devs.value('(approvalAuth/text())[1]','nvarchar(150)') AS approvalAuth
,devs.value('(istest/text())[1]','nvarchar(150)') AS istest
,TheXml.value('(/applicant/Numerator/text())[1]','NUMERIC') AS Numerator
FROM (SELECT CAST(@YourXML.value('.','nvarchar(max)') AS XML)) AS tbl(TheXml)
CROSS APPLY TheXml.nodes('/applicant/deviations') A(devs);
结果
+----------------+-----------------+--------------+--------------+--------+-----------+
| PartyReference | deviMessage | deviSeverity | approvalAuth | istest | Numerator |
+----------------+-----------------+--------------+--------------+--------+-----------+
| string | read this data | L2 | CCM | NO | 0 |
+----------------+-----------------+--------------+--------------+--------+-----------+
| string | read thid data2 | L2 | CCM | NO | 0 |
+----------------+-----------------+--------------+--------------+--------+-----------+
推荐阅读
- r - Shiny Leaflet R 无法正确更改圆形标记的颜色
- spring - 在 Spring Boot 中加载 chromedriver
- node.js - Heroku postgres 爱好基本内存限制
- tfs - MSTest failed to publish test result with error 此时无法启动异步操作
- c - “控件可能到达非无效函数的末尾”错误消息
- javascript - 在 NuxtJS 中托管一组脚本
- r - 布朗运动的桥采样
- r - 如何在使用 ggplot2 的两行中使用表达式时添加三行 y 轴文本?
- performance - AVX中绝对值的浮点比较
- java - 您将如何向命令添加可选参数?