SQL Server Openxml从具有前缀的复杂xml中选择数据

tuwxkamq  于 2022-12-26  发布在  其他
关注(0)|答案(1)|浏览(103)

XML结构和预期结果如下所示,我可以遍历所有数据,但无法从xpath中选取xml元素,因为item节点的前缀为"a:"

我尝试了以下操作,但都不起作用:
第一:

Set @path =  N'/root/JSON/schData/ESU/item[1]/ESU_Bl_0'
        SELECT * from openxml (@hDoc, @path, 2)
        with            
        (   
             ID         nvarchar(200) 
            ,Item_0_1   nvarchar(200) 
            ,Item_0_2   nvarchar(200) 
        )

第二:

Set @path =  N'/root/JSON/schData/ESU/a:item/ESU_Bl_0'
        SELECT * from openxml (@hDoc, @path, 2)
        with            
        (   
             ID         nvarchar(200) 
            ,Item_0_1   nvarchar(200) 
            ,Item_0_2   nvarchar(200) 
        )

第三:

Set @path =  N'/root/JSON/schData/ESU//*:item/ESU_Bl_0'
        SELECT * from openxml (@hDoc, @path, 2)
        with            
        (   
             ID         nvarchar(200) 
            ,Item_0_1   nvarchar(200) 
            ,Item_0_2   nvarchar(200) 
        )

第四:

Set @path =  N'/root/JSON/schData/ESU/a[1]/ESU_Bl_0'
        SELECT * from openxml (@hDoc, @path, 2)
        with            
        (   
             ID         nvarchar(200) 
            ,Item_0_1   nvarchar(200) 
            ,Item_0_2   nvarchar(200) 
        )

请帮助您的专业知识,谢谢!!
更新:评论中要求的xml示例-

<root type="object">
    <JSON type="object">
        <schData type="object">
            <ESU type="object">
                <a:item xmlns:a="item" item="0" type="object">
                    <ESU_Bl_0 type="object">
                        <ID type="number">1</ID>
                        <Item_0_1 type="string">A</Item_0_1>
                        <Item_0_2 type="string">B</Item_0_2>
                    </ESU_Bl_0>
                    <ESU_Bl_1 type="object">
                        <ID type="number">2</ID>
                        <Item_1_1 type="string">C</Item_1_1>
                        <Item_1_2 type="string">D</Item_1_2>
                    </ESU_Bl_1>
                </a:item>
                <a:item xmlns:a="item" item="1" type="object">
                    <ESU_Bl_0 type="object">
                        <ID type="number">3</ID>
                        <Item_0_1 type="string">E</Item_0_1>
                        <Item_0_2 type="string">F</Item_0_2>
                    </ESU_Bl_0>
                    <ESU_Bl_1 type="object">
                        <ID type="number">4</ID>
                        <Item_1_1 type="string">G</Item_1_1>
                        <Item_1_2 type="string">H</Item_1_2>
                    </ESU_Bl_1>
                </a:item>
            </ESU>
        </schData>
    </JSON>
</root>
yhived7q

yhived7q1#

请尝试以下解决方案。
保留Microsoft专有的OPENXML()及其伙伴sp_xml_preparedocumentsp_xml_removedocument只是为了向后兼容过时的SQL Server 2000。它们的使用减少到极少数边缘情况。从SQL Server 2005开始,强烈建议重写SQL并将其切换到XQuery。

    • SQL语言**
DECLARE @xml XML =
N'<root type="object">
    <JSON type="object">
        <schData type="object">
            <ESU type="object">
                <a:item xmlns:a="item" item="0" type="object">
                    <ESU_Bl_0 type="object">
                        <ID type="number">1</ID>
                        <Item_0_1 type="string">A</Item_0_1>
                        <Item_0_2 type="string">B</Item_0_2>
                    </ESU_Bl_0>
                    <ESU_Bl_1 type="object">
                        <ID type="number">2</ID>
                        <Item_1_1 type="string">C</Item_1_1>
                        <Item_1_2 type="string">D</Item_1_2>
                    </ESU_Bl_1>
                </a:item>
                <a:item xmlns:a="item" item="1" type="object">
                    <ESU_Bl_0 type="object">
                        <ID type="number">3</ID>
                        <Item_0_1 type="string">E</Item_0_1>
                        <Item_0_2 type="string">F</Item_0_2>
                    </ESU_Bl_0>
                    <ESU_Bl_1 type="object">
                        <ID type="number">4</ID>
                        <Item_1_1 type="string">G</Item_1_1>
                        <Item_1_2 type="string">H</Item_1_2>
                    </ESU_Bl_1>
                </a:item>
            </ESU>
        </schData>
    </JSON>
</root>';

;WITH XMLNAMESPACES('item' AS a)
SELECT c.value('(ID/text())[1]', 'INT') AS ID
    , c.value('(Item_0_1/text())[1]', 'VARCHAR(20)') AS Item_0_1
    , c.value('(Item_0_2/text())[1]', 'VARCHAR(20)') AS Item_0_2
FROM @xml.nodes('/root/JSON/schData/ESU/a:item[@item="0"]/ESU_Bl_0') AS t(c);
    • 产出**

| 识别号|项目_0_1|项目_0_2|
| - ------| - ------| - ------|
| 1个|A类|乙|

相关问题