Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 13 Nov '23, 11:51

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k364766
accept rate: 21%


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'
permanent link

answered 13 Nov '23, 12:56

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824
accept rate: 34%

edited 14 Nov '23, 05:46

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×29
×22

question asked: 13 Nov '23, 11:51

question was seen: 232 times

last updated: 14 Nov '23, 05:46