sql-server - tsql for xml，复杂的 xml

Question

我正在尝试获取一些 xml，但看起来我的结构不适合我检查的任何学习示例，我无法从 xml.nodes 中选择 ...。如何获取度量 id (aba,cbp) 的所有值和 ABA 的值 den (=777)。


这是我的第一种方法，它不起作用

    SELECT xmldata
 , n0.b.value('(@type)[1]', 'varchar(100)') as  c1   --<  'GGM'
 , n.b.value('(@id)[1]', 'varchar(100)') as  m1   --<  ABA
 , n.b.value('(@id)[2]', 'varchar(100)') as  m2   --<  CBP
 FROM z
 CROSS APPLY z.xmldata.nodes('//submission/component/audit/data/measures/measure') AS n(b)
 CROSS APPLY z.xmldata.nodes('//submission/component') AS n0(b)

以下是测试负载:

   DECLARE @MyXML XML;
    SET @MyXML =
     ('<?xml version="1.0" encoding="UTF-8"?>
    <submission vendor-id="9999" guid="1234-5678-4578-4784" xmlns="http://www.ncqa.org/ns/2006/idss/hedis">
    <metadata>
        <version>41</version>
        <timestamp/>
        <sub-id>1434588</sub-id>
        <org-id/>
        <org-name/>
        <product-line>NC15</product-line>
        <reporting-product/>
        <special-project/>
        <special-area/>
        <hcfa-contract/>
        <hcfa-area/>
        <year-end-date>12/31/2016</year-end-date>
        <audit>true</audit>
    </metadata>
    <component type="GGM">
    <audit>
       <measures>
            <measure id="aba">
            <reported>true</reported>
            <benefit>true</benefit>
            <data-elements>
            <data-element id="rate">
            <audit-designation>false</audit-designation>
            <comment/>
            </data-element>
            </data-elements>
            </measure>
            <measure id="cbp">
            <reported>true</reported>
            <benefit>true</benefit>
            <data-elements>
            <data-element id="rate">
            <audit-designation>false</audit-designation>
            <comment/>
            </data-element>
            </data-elements>
            </measure>

       </measures>

       <data>
       <measures>
            <measure id="aba" measure-version-id="44444-222222-33333">
            <data-elements>
                <data-element id="den">
                <value>777</value>
                </data-element>
                <data-element id="elignu">
                <value>48</value>
                </data-element>
                <data-element id="eligpop">
                <value>777</value>
                </data-element>
            </data-elements>

        </measure>
        <measure id="cbp" measure-version-id="11111-222222-33333">
            <data-elements>
            <data-element id="admexc">
            <value>0</value>
            </data-element>
            <data-element id="collmeth">
            <value>H</value>
            </data-element>
            <data-element id="dentot">
            <value>355</value>
            </data-element>
            <data-element id="eligtot">
            <value>123</value>
            </data-element>
            <data-element id="empexc">
            <value>0</value>
            </data-element>


            </data-elements>
            </measure>  

       </measures>

       </data>
       </audit>
    </component> 
    </submission>')

     SELECT @MyXML as xmldata into z;

Answer 1

您显示的错误消息:

Msg 9506, Level 16, State 1, Line 1 The XMLDT method 'nodes' can only be invoked on columns of type xml.

... 指向包含 XML 但输入不正确的表的列。您必须先进行转换(例如 CAST StringXML AS XML )。如果可能，您应该 店铺 XML 格式的值。这是 快多了!!

关于阅读您的 XML

在您的顶级节点 <submission>有一个默认命名空间 xmlns="something" .读取 XML 时，您必须声明此命名空间，或者必须使用命名空间通配符 ( *:)。一般建议是:尽可能具体!

试试这样:

从 <metadata> 中读取一个值:

WITH XMLNAMESPACES(DEFAULT 'http://www.ncqa.org/ns/2006/idss/hedis')
SELECT @MyXML.value(N'(/submission/metadata/version)[1]',N'int') AS metadata_version;

--您可能需要的查询

WITH XMLNAMESPACES(DEFAULT 'http://www.ncqa.org/ns/2006/idss/hedis')
SELECT m.value(N'@id',N'nvarchar(max)')
      ,m.value(N'(data-elements/data-element[@id="den"]/value/text())[1]',N'int')
FROM @MyXML.nodes(N'/submission/component/audit/data/measures/measure') AS A(m); 

懒人也一样(不推荐)

SELECT m.value(N'@id',N'nvarchar(max)')
      ,m.value(N'(*:data-elements/*:data-element[@id="den"]/*:value/text())[1]',N'int')
FROM @MyXML.nodes(N'//*:data//*:measure') AS A(m);