I want to extract location id from give xml, and also want to put condition that extract it if location does exists.
<?xml version="1.0" encoding="UTF-8"?>
<id>1006221342207</id>
<name>Hitesh Modha</name>
<email>[email protected]</email>
<location>
<id>115440481803904</id>
<name>Ahmedabad, India</name>
</location>
I have tried :
SET location = ExtractValue(xml, '//locations//id');
SELECT ExtractValue(location, '//id');
but it is not working. Please help
The SQL
SELECT
ExtractValue('<?xml version="1.0" encoding="UTF-8"?>
<id>1006221342207</id>
<name>Hitesh Modha</name>
<email>[email protected]</email>
<location>
<id>115440481803904</id>
<name>Ahmedabad, India</name>
</location>', '//location//id');
returns only an empty result, because the path //locations//id is empty for the XML fragment you use.
You're more likely looking for '/location/id' for the xpath expression, as it will return the id text() value: 115440481803904
Next mistake you do is that you think ExtractValue would return an XML fragment you can re-run ExtractValue on. That's just not the case (only if - which is not the case in your example - there would have been XML encoded as CDATA in the located text() node).
All the details are explained in detail here:
If you can not work around these misconceptions, there is no further suggestion I can give you.
