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