UDF for deleting Tag from XML in MySQL Stored Procedure!!!!!!!!!

[Abhishek Soni]

I’ve been trying to migrate “Stored Procedures” from Oracle to MySQL.
In my stored procedures am generating XML at run time.

In Oracle am using insertChildXML, appendChildXML, deleteXML and
extractvalue functions of Oracle library.

In MySQL I’ve succeeded in replacing functionalities of insertChildXML
and appendChildXML with UDF’s of lib_mysqludf_xql library. But I was
not able to find any UDF in lib_mysqludf_xql with which I could
replace functionality of deleteXML and extractvalue


Here is a part of my Oracle Stored Procedure for which I want to
implement in MySQL :-



SELECT deleteXML(alias_xml,'/IFC/
InitialFilterCriteria') INTO alias_xml FROM dual WHERE
extractvalue(alias_xml,'/IFC/InitialFilterCriteria/IfcId')=3;



SELECT extract(alias_xml,'/IFC/
InitialFilterCriteria[IfcId='2) into alias_temp_xml from dual;



SELECT deleteXML(alias_temp_xml,'/
InitialFilterCriteria/IfcId') INTO alias_temp_xml FROM dual;



The first query deletes tag InitialFilterCriteria of the XML for which
the child tag IfcId = 3



Second query fetches tags for which the child tag IfcId = 2.



Third query deletes tag IfcId from the resulting XML.



In above queries alias_xml is a CLOB type variable which contains XML
fetched from one of my Database Table column and

alias_temp_xml is a temporary CLOB type variable which am using in
later steps of procedure.





This is what alias_xml shall contain before the above queries are
run :-



<IFC>

<InitialFilterCriteria>

<IfcId>2</IfcId>

<Priority>3</Priority>

<TriggerPoint>

<ConditionTypeCNF>0</
ConditionTypeCNF>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>1</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>2</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>0</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>1</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

</TriggerPoint>

<Shared_IFC_Flag>1</Shared_IFC_Flag>

<ApplicationServer>


<ServerName>sip:a...@rancoretech.com</ServerName>

<DefaultHandling>0</
DefaultHandling>

<ServiceInfo>8</ServiceInfo>

</ApplicationServer>

<ProfilePartIndicator>1</ProfilePartIndicator>

</InitialFilterCriteria>

<InitialFilterCriteria>

<IfcId>3</IfcId>

<Priority>1</Priority>

<TriggerPoint>

<ConditionTypeCNF>0</
ConditionTypeCNF>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>2</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>0</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>0</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>1</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

</TriggerPoint>

<Shared_IFC_Flag>1</Shared_IFC_Flag>

<ApplicationServer>


<ServerName>sip:a...@rancoretech.com</ServerName>

<DefaultHandling>0</
DefaultHandling>

<ServiceInfo>8</ServiceInfo>

</ApplicationServer>

<ProfilePartIndicator>1</ProfilePartIndicator>

</InitialFilterCriteria>

<InitialFilterCriteria>

<IfcId>4</IfcId>

<Priority>2</Priority>

<TriggerPoint>

<ConditionTypeCNF>0</
ConditionTypeCNF>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>2</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>0</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>0</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>1</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

</TriggerPoint>

<Shared_IFC_Flag>1</Shared_IFC_Flag>

<ApplicationServer>


<ServerName>sip:a...@rancoretech.com</ServerName>

<DefaultHandling>0</
DefaultHandling>

<ServiceInfo>8</ServiceInfo>

</ApplicationServer>

<ProfilePartIndicator>1</ProfilePartIndicator>

</InitialFilterCriteria>

</IFC>



This is what alias_temp_xml will contain after the queries have run :-



<InitialFilterCriteria>

<Priority>3</Priority>

<TriggerPoint>

<ConditionTypeCNF>0</
ConditionTypeCNF>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>1</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>2</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>1</
ConditionNegated>

<Group>0</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

<SPT>

<ConditionNegated>0</
ConditionNegated>

<Group>1</Group>

<Method>INVITE</
Method>

<Extension>


<RegistrationType>0</RegistrationType>

</Extension>

</SPT>

</TriggerPoint>

<Shared_IFC_Flag>1</Shared_IFC_Flag>

<ApplicationServer>


<ServerName>sip:a...@rancoretech.com</ServerName>

<DefaultHandling>0</
DefaultHandling>

<ServiceInfo>8</ServiceInfo>

</ApplicationServer>

<ProfilePartIndicator>1</ProfilePartIndicator>

</InitialFilterCriteria>



I’ve been trying to work out a solution but have not succeeded yet.
I’ve even tried UpdateXML and REPLACE functions of MySQL but they
don’t provide all the functionality that I need.

If there is any udf in lib_mysqludf_xql with which I can successfully
implement my stored procedure then please let me know. And if there’s
not one, then is there any thought being given to add such a function
in near future to lib_mysqludf_xql library ???



I hope that I’ve been able to clearly explain my query.

[Arnold Daniels]

Hi Abhishek,

As I already told you, lib_mysqludf_xql can only build XML. It can not
modifiy existing XML. This will never be part of lib_mysqludf_xql
because of technical reasons, which I already explained.

Please have a closer look at UpdateXML and/or ask in the MySQL XML
forum at http://forums.mysql.com/list.php?44

Best regards,
Arnold