Openxml select data from complex xml having prefix

Question

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

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Dec 23, 2022 at 18:23
Your XML is not wel-formed. "Parsing error in Untitled2.xml: expected end of tag 'Item_1_1' " Please fix it. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Dec 23, 2022 at 18:30
@YitzhakKhabinsky, its fixed, sorry the xml was too huge, while reducing I made that mistake. — Bilal Siddiqui
– Bilal Siddiqui, Commented Dec 23, 2022 at 18:31

Yitzhak Khabinsky · Accepted Answer · 2022-12-26 14:28:25Z

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

SQL #2

XQuery .nodes() method accepts just literal parameter. But it is easy to parameterize via sql:variable() or sql:column(). Please see below.

DECLARE @Counter INT = 0;

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

Output

ID	Item_0_1	Item_0_2
1	A	B

Hi @Yitzhak, I am facing an issue while dynamizing your solution, have updated the question with the new issue,

shoeb khan · Accepted Answer · 2022-12-26 05:57:48Z

1

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.

answered Dec 26, 2022 at 5:57

shoeb khan

525 bronze badges

Collectives™ on Stack Overflow

Openxml select data from complex xml having prefix

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related