OPENXML to get content by using element and attribute name In this SQL Server OPENXML example:
declare @xmldata xml    

set @xmldata = 
'&lt;?xml version=&quot;1.0&quot;?&gt;
&lt;wddxPacket version=&quot;1.0&quot;&gt;
    &lt;data&gt;
        &lt;var dimension=&quot;task&quot;&gt;&lt;string&gt;Task 1&lt;/string&gt;&lt;/var&gt;
        &lt;var dimension=&quot;startdate&quot;&gt;&lt;string&gt;2020-11-14&lt;/string&gt;&lt;/var&gt;
    &lt;/data&gt;
    &lt;data&gt;
        &lt;var dimension=&quot;startdate&quot;&gt;&lt;string&gt;2020-11-15&lt;/string&gt;&lt;/var&gt;
        &lt;var dimension=&quot;task&quot;&gt;&lt;string&gt;Task 2&lt;/string&gt;&lt;/var&gt;
    &lt;/data&gt;
&lt;/wddxPacket&gt;'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '//wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[1]/string',
            startdate     varchar(200) 'var[2]/string'
    )

EXEC sp_xml_removedocument @hDoc

This is the current output:
task         startdate
------       ----------
Task 1       2020-11-14
2020-11-15   Task 2   

But I really want this output:
task         startdate
------       ----------
Task 1       2020-11-14
Task 2       2020-11-15

To identify the content, I cannot use &quot;var&quot; alone, because all elements of interest are named &quot;var&quot;. I cannot use position [1] or [2], because the position numbers are inconsistent in the XML, resulting in the current mixed-up output. How do I identify the elements based on &quot;var&quot; and the &quot;dimension&quot; attribute names of &quot;task&quot; or &quot;startdate&quot;?
You need to use a different XPath expression to match the attribute values, eg var[@dimension=&quot;task&quot;]/string.
Like this:
declare @xmldata xml    

set @xmldata = 
'&lt;?xml version=&quot;1.0&quot;?&gt;
&lt;wddxPacket version=&quot;1.0&quot;&gt;
    &lt;data&gt;
        &lt;var dimension=&quot;task&quot;&gt;&lt;string&gt;Task 1&lt;/string&gt;&lt;/var&gt;
        &lt;var dimension=&quot;startdate&quot;&gt;&lt;string&gt;2020-11-14&lt;/string&gt;&lt;/var&gt;
    &lt;/data&gt;
    &lt;data&gt;
        &lt;var dimension=&quot;startdate&quot;&gt;&lt;string&gt;2020-11-15&lt;/string&gt;&lt;/var&gt;
        &lt;var dimension=&quot;task&quot;&gt;&lt;string&gt;Task 2&lt;/string&gt;&lt;/var&gt;
    &lt;/data&gt;
&lt;/wddxPacket&gt;'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '/wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[@dimension=&quot;task&quot;]/string',
            startdate     varchar(200) 'var[@dimension=&quot;startdate&quot;]/string'
    )

EXEC sp_xml_removedocument @hDoc

outputs
task    startdate
------- ------------
Task 1  2020-11-14
Task 2  2020-11-15