SQL Anywhere 17: OpenXML() and list of values

[fvestjens]

Is it possible to parse the XML below using OpenXML()?

<fields>
    <field id="orderid">
        <value>3212124</value>
    </field>
    <field id="damagePic">
        <values>
            <value-item mimetype="image/jpeg">c0f92afe-f688-49f1-9946-b7015efbe977</value-item>
            <value-item mimetype="image/jpeg">0119dd2c-50fd-4c8e-ab05-3771b7f5c41c</value-item>
            <value-item mimetype="image/jpeg">13d9dee8-13b2-486c-b216-aa5085bdf5cc</value-item>
            <value-item mimetype="image/jpeg">75bc8337-79c1-47f5-bf12-79fde850479a</value-item>
            <value-item mimetype="image/jpeg">333c3bdb-9e90-4766-ac8e-28405bd74545</value-item>
            <value-item mimetype="image/jpeg">63e088f1-9e0e-4aba-88cb-04bd44b32c95</value-item>
        </values>
    </field>
</fields> 

The query below will return only the first value-item from the list

select *
from openxml(in_XML,'fields/field')
with (Field         varchar(128) './@id'
     ,Value         varchar(128) 'value'
     ,ValueItem     varchar(128) 'values/value-item'
     ,ValueMimeType varchar(32)  'values/value-item/@mimetype');

[VolkerBarth]

You may have a look at my older question, where I learnt to focus XPath expressions on the "deepest node level" whose values I want to retrieve - and if I used XPath expressions on a higher level, OpenXML would only return the first of a sequence of childs with the same name (as you seem to face, too):

https://sqlanywhere-forum.sap.com/questions/15340

This will list all value items:

select *
from openxml(in_XML,'//value-item')
with (Field         varchar(128) '../../@id'
     ,Value         varchar(128) '../../@value'
     ,ValueItem     varchar(128) '.'
     ,ValueMimeType varchar(32)  '@mimetype');

returns

Field;Value;ValueItem;ValueMimeType
'damagePic';;'c0f92afe-f688-49f1-9946-b7015efbe977';'image/jpeg'
'damagePic';;'0119dd2c-50fd-4c8e-ab05-3771b7f5c41c';'image/jpeg'
'damagePic';;'13d9dee8-13b2-486c-b216-aa5085bdf5cc';'image/jpeg'
'damagePic';;'75bc8337-79c1-47f5-bf12-79fde850479a';'image/jpeg'
'damagePic';;'333c3bdb-9e90-4766-ac8e-28405bd74545';'image/jpeg'
'damagePic';;'63e088f1-9e0e-4aba-88cb-04bd44b32c95';'image/jpeg'

You might need two separate OpenXML calls if you want to combine nodes from different DOM trees (say, the orderid here), such as

select *
from
   openxml(in_XML,'//field')
   with (NodeID        bigint '@mp:id'
        ,Field         varchar(128) '@id'
        ,Value         varchar(128) './value'
   ) Fields
left join 
   openxml(in_XML,'//value-item')
   with (FieldNodeID   bigint '../../@mp:id'
        ,Field         varchar(128) '../../@id'
        ,NodeID        bigint '@mp:id'
        ,ValueItem     varchar(128) '.'
        ,ValueMimeType varchar(32)  '@mimetype'
   ) ValueItems on Fields.NodeID = ValueItems.FieldNodeID
order by Fields.NodeID, ValueItems.NodeID;

returns

NodeID;Field;Value;FieldNodeID;Field;NodeID;ValueItem;ValueMimeType
20;'orderid';'3212124';;;;;
89;'damagePic';;89;'damagePic';146;'c0f92afe-f688-49f1-9946-b7015efbe977';'image/jpeg'
89;'damagePic';;89;'damagePic';242;'0119dd2c-50fd-4c8e-ab05-3771b7f5c41c';'image/jpeg'
89;'damagePic';;89;'damagePic';338;'13d9dee8-13b2-486c-b216-aa5085bdf5cc';'image/jpeg'
89;'damagePic';;89;'damagePic';434;'75bc8337-79c1-47f5-bf12-79fde850479a';'image/jpeg'
89;'damagePic';;89;'damagePic';530;'333c3bdb-9e90-4766-ac8e-28405bd74545';'image/jpeg'
89;'damagePic';;89;'damagePic';626;'63e088f1-9e0e-4aba-88cb-04bd44b32c95';'image/jpeg'