Re: Using ExtractValue( ) function to insert XML data in a table in MySQL

[Rick James]

Based on the following, I suggest it won't work as expected:

SET @xml :=
"<ManageAsset>
<Asset>
<SiteGroup>Chicago</SiteGroup>
<SiteNumber>4524</SiteNumber>
<AssetCategory>Passenger Cars</AssetCategory>
<Address>Address 1</Address>
<AssetNumber>Tango</AssetNumber>
</Asset>
<Asset>
<SiteGroup>Cincinnati</SiteGroup>
<SiteNumber>5360</SiteNumber>
<AssetCategory>Passenger Cars</AssetCategory>
<Address>Address 2</Address>
<AssetNumber>Mango</AssetNumber>
</Asset>
</ManageAsset>";

select ExtractValue(@xml,'//ManageAsset/Asset/SiteGroup') as SiteGroup,
ExtractValue(@xml,'//ManageAsset/Asset/SiteNumber') as SiteNumber,
ExtractValue(@xml,'//ManageAsset/Asset/AssetCategory') as AssetCategory,
ExtractValue(@xml,'//ManageAsset/Asset/Address') as Address,
ExtractValue(@xml,'//ManageAsset/Asset/AssetNumber') as AssetNumber;

+--------------------+------------+-------------------------------+---------------------+-------------+
| SiteGroup          | SiteNumber | AssetCategory                 | Address             | AssetNumber |
+--------------------+------------+-------------------------------+---------------------+-------------+
| Chicago Cincinnati | 4524 5360  | Passenger Cars Passenger Cars | Address 1 Address 2 | Tango Mango |
+--------------------+------------+-------------------------------+---------------------+-------------+

Suggest you use this technique for testing other formulations.