Server节点解析

Question

SQL Server中的XML解析非常新。这是我所拥有的，也是我想做的。

我有一个有许多行的表，与此类似：

+-------------------+------------------------------------+
| EDI_Assessment_ID |              XML_TEXT              |
+-------------------+------------------------------------+
|             12345 | text column containing XML         |
|             12346 | text column containing XML         |
+-------------------+------------------------------------+

XML_Text列有一个类似于这种结构的大型XML文本(我已经简化并只粘贴了它的相关部分：

<Assessment>
<ADLs>
  <ADL_Group>
     <ADL>bathing</ADL>
     <Mapped_ADL Source="Calypso">Bathing</Mapped_ADL>
     <ADL_Level>Requires only equipment to complete ADL</ADL_Level>
     <Mapped_ADL_Level Source="Calypso">Independent</Mapped_ADL_Level>
     <ADL_Equipment>HH shower</ADL_Equipment>
     <ADL_Assisted_By_Info>
        <ADL_Assisted_By>No one</ADL_Assisted_By>
     </ADL_Assisted_By_Info>
  </ADL_Group>
  <ADL_Group>
     <ADL>Continence-Bowel</ADL>
     <Mapped_ADL Source="Calypso">Continence</Mapped_ADL>
     <ADL_Level>Independent</ADL_Level>
     <Mapped_ADL_Level Source="B/A">Independent</Mapped_ADL_Level>
     <ADL_Equipment />
     <ADL_Assisted_By_Info>
        <ADL_Assisted_By>No one</ADL_Assisted_By>
     </ADL_Assisted_By_Info>
  </ADL_Group>
</Assessment>

如何通过XML解析表中的每一行以返回：

1. (洗澡，大碗)和
2. the ADL_Assisted_By_Info

我正在寻找返回类似于以下内容的结果集：

+-------------------+-------------+----------------------+------------------+----------------------+
| EDI_Assessment_ID |   Bathing   | ADL_Assisted_By_Info | Continence-Bowel | ADL_Assisted_By_Info |
+-------------------+-------------+----------------------+------------------+----------------------+
|             12345 | Independent | No one               | Independent      | No one               |
+-------------------+-------------+----------------------+------------------+----------------------+

Answer 1

这些解决方案所依赖的是OP的示例数据中的而不是 true；数据类型text的列具有有效的 XML。示例数据不是，因此此解决方案不会与它们提供的示例数据相反。

事实上，如果OP的所有示例数据都是格式不良的XML，那么Server在这里是完全错误的选择。理想情况下，它们应该首先修复它们的数据，然后将数据类型更改为xml，以便更多糟糕的XML不能插入到数据库中。

如果，无论出于什么原因，他们无法做到这一点，那么他们将需要找到一个不同的解决方案。然而，Server不是解决方案。您将需要一些非常好的字符串操作，并以这种方式计算出值。如果您在(大型) dataset值下执行此操作，则该过程可能会减慢到爬行。

不管怎样，说到重点。注意注释。有两个解决方案，第一个(除了有效性之外)假设bathing节点总是第一个ADL_GROUP元素，而Continence-Bowel总是第二个：

WITH VTE AS(
    SELECT 12345 AS ID,
           CONVERT(text,
'<Assessment>
<ADLs>
  <ADL_Group>
     <ADL>bathing</ADL>
     <Mapped_ADL Source="Calypso">Bathing</Mapped_ADL>
     <ADL_Level>Requires only equipment to complete ADL</ADL_Level>
     <Mapped_ADL_Level Source="Calypso">Independent</Mapped_ADL_Level>
     <ADL_Equipment>HH shower</ADL_Equipment>
     <ADL_Assisted_By_Info>
        <ADL_Assisted_By>No one</ADL_Assisted_By>
     </ADL_Assisted_By_Info>
  </ADL_Group>
  <ADL_Group>
     <ADL>Continence-Bowel</ADL>
     <Mapped_ADL Source="Calypso">Continence</Mapped_ADL>
     <ADL_Level>Independent</ADL_Level>
     <Mapped_ADL_Level Source="B/A">Independent</Mapped_ADL_Level>
     <ADL_Equipment />
     <ADL_Assisted_By_Info>
        <ADL_Assisted_By>No one</ADL_Assisted_By>
     </ADL_Assisted_By_Info>
  </ADL_Group>
</ADLs>' + --I have added this line to make the XML valid. The sample you have will NOT work, as it is not valid XML
'</Assessment>') AS XML_Text
)
SELECT V.ID,
       X.XML_Type,
       T.AA.value('(ADL_Group/Mapped_ADL_Level/text())[1]','varchar(30)') AS Bathing,
       T.AA.value('(ADL_Group/ADL_Assisted_By_Info/ADL_Assisted_By/text())[1]','varchar(30)') AS ADL_Assisted_By_Info,
       T.AA.value('(ADL_Group/Mapped_ADL_Level/text())[2]','varchar(30)') AS ContinenceBowel,
       T.AA.value('(ADL_Group[2]/ADL_Assisted_By_Info/ADL_Assisted_By/text())[1]','varchar(30)') AS ADL_Assisted_By_Info
FROM VTE V
     CROSS APPLY (VALUES(TRY_CONVERT(xml, V.XML_Text))) X(XML_Type)
     CROSS APPLY X.XML_Type.nodes('/Assessment/ADLs') T(AA);

但是，如果这不是真的，并且可能有其他节点，具有不同的值，那么您可以为SELECT执行以下操作(不包括CTE)：

SELECT V.ID,
       X.XML_Type,
       B.AG.value('(Mapped_ADL_Level/text())[1]','varchar(30)') AS Bathing,
       B.AG.value('(ADL_Assisted_By_Info/ADL_Assisted_By/text())[1]','varchar(30)') AS ADL_Assisted_By_Info,
       CB.AG.value('(Mapped_ADL_Level/text())[1]','varchar(30)') AS ContinenceBowel,
       CB.AG.value('(ADL_Assisted_By_Info/ADL_Assisted_By/text())[1]','varchar(30)') AS ADL_Assisted_By_Info
FROM VTE V
     CROSS APPLY (VALUES(TRY_CONVERT(xml, V.XML_Text))) X(XML_Type)
     CROSS APPLY X.XML_Type.nodes('/Assessment/ADLs/ADL_Group') B(AG)
     CROSS APPLY X.XML_Type.nodes('/Assessment/ADLs/ADL_Group') CB(AG)
WHERE B.AG.value('(ADL/text())[1]','varchar(30)') = 'bathing'
  AND CB.AG.value('(ADL/text())[1]','varchar(30)') = 'Continence-Bowel';