Reading XML attribute values using OPENXML

Question

For the following .nodes() approach, I need an equivalent OPENXML approach. The Attributes will be different and can not be hard-coded.

DECLARE @Xml XML='<row>
                     <DeletedVal>
                        <row attribute1="value1" attribute2="value2"/>
                     </DeletedVal>
                   </row>';

SELECT x1.y.value('local-name(.)', 'VARCHAR(30)') AS [Key]
       , x1.y.value('.', 'VARCHAR(MAX)')          AS [Value]
FROM   @Xml.nodes('/row/DeletedVal//@*') x1(y)

Output:

Key                            Value
------------------------------ ------
attribute1                     value1
attribute2                     value2

The following OPENXML approach needs fixing, where I am not sure how to get the attributes.

DECLARE @DocHandle INT
EXEC sp_xml_preparedocument
  @DocHandle OUTPUT
  , @Xml;

SELECT *
FROM   OPENXML (@docHandle, N'/row/DeletedVal//@*')
          WITH ([Key]     VARCHAR(10) 'key' --- This line needs editing
                , [Value] VARCHAR(10) '.')

EXEC Sp_xml_removedocument
  @DocHandle;

Output:

Key        Value
---------- ----------
NULL       value1
NULL       value2

Why do you want to use the far older functionality and *not* XQUERY? — Thom A, Jul 29 '20 at 08:31
I am not aware of XQUERY also my server run SQL Server 2012. Will my SQL server version support XQUERY? — RandomUser, Jul 29 '20 at 08:36
XQUERY has been supported since SQL Server 2005 (if i recall correctly) and is what you are using in the first statement. — Thom A, Jul 29 '20 at 08:38
When getting execution plan, the OPENXML took just 5% vs. 95% for the XQUERY(.nodes()). So OPENXML is better right? — RandomUser, Jul 29 '20 at 08:46
Lower numbers do not mean "better", no. XQUERY is the way to go. The old `sp_xml_preparedocument` are more still in SQL Server for back compatibility than anything. — Thom A, Jul 29 '20 at 08:50

score 0 · Answer 1 · answered Jul 29 '20 at 08:34

Referring this link, I found the solution as below:

DECLARE @Xml XML='<row><DeletedVal><row attribute1="value1" attribute2="value2"/></DeletedVal></row>';
DECLARE @DocHandle INT

EXEC sp_xml_preparedocument
  @DocHandle OUTPUT
  , @Xml;

SELECT *
FROM   OPENXML (@docHandle, N'/row/DeletedVal//@*')
          WITH ([Key]     VARCHAR(10) '@mp:localname'
                , [Value] VARCHAR(10) '.')

EXEC Sp_xml_removedocument
  @DocHandle;

score 0 · Accepted Answer · answered Jul 29 '20 at 13:36

As @Larnu correctly pointed out, 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.

It is strongly recommended to re-write your SQL and switch it to XQuery. It is available in MS SQL Server starting from 2005 onwards.

I made some performance improvements to your T-SQL by removing the //@* (search for descendants everywhere down) from the .nodes() XQuery method.

DECLARE @Xml XML = 
N'<row>
    <DeletedVal>
        <row attribute1="value1" attribute2="value2"/>
    </DeletedVal>
</row>';

SELECT c.value('local-name(.)', 'VARCHAR(30)') AS [Key]
    , c.value('.', 'VARCHAR(MAX)') AS [Value]
FROM   @Xml.nodes('/row/DeletedVal/row/@*') AS t(c);

Output

+------------+--------+
|    Key     | Value  |
+------------+--------+
| attribute1 | value1 |
| attribute2 | value2 |
+------------+--------+

Reading XML attribute values using OPENXML

2 Answers2