6

In this SQL Server OPENXML example:

declare @xmldata xml    

set @xmldata = 
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

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 "var" alone, because all elements of interest are named "var". 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 "var" and the "dimension" attribute names of "task" or "startdate"?

8

You need to use a different XPath expression to match the attribute values, eg var[@dimension="task"]/string.

Like this:

declare @xmldata xml    

set @xmldata = 
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

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

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

EXEC sp_xml_removedocument @hDoc

outputs

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.