Openxml select data from complex xml having prefix-CodePudding

The XML structure and expected result is below, I can loop through all the data but unable to pick xml elements from xpath as the item node is prefixed with "a:"

I have tried following but none is working:

#1:

        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) 
        )

#2:

        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) 
        )

#3:

        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) 
        )

#4:

        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) 
        )

Please help with your expertise, Thanks !!

UPDATE: xml sample as asked in comment -

<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>

UPDATE:

The solution proposed by Yitzhak is Okay with static query but when I try to inject the Counter variable dynamically I am getting following errror.

Line 41 [Batch Start Line 0]
The argument 1 of the XML data type method "nodes" must be a string literal.

Here is how I am trying:

;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="' @Counter '"]/ESU_Bl_0') AS t(c);

CodePudding user response：

Please try the following solution.

Microsoft proprietary OPENXML() and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to 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);

Output

ID	Item_0_1	Item_0_2
1	A	B

CodePudding user response：

So for making it dynamic, here is what you can do with Yitzhak's solution:

;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=sql:variable("@Counter")]/ESU_Bl_0') AS t(c);

This way the Counter will be dynamically inserted.