OraFAQ Forum: JDeveloper, Java & XML » Using DELETEXML SQL function

Home » Developer & Programmer » JDeveloper, Java & XML » Using DELETEXML SQL function (ORACLE 11GR2 (11.2.0.3) ; AIX )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using DELETEXML SQL function [message #585938]

Fri, 31 May 2013 02:40

jelencik
Messages: 8
Registered: May 2013
Location: TOULOUSE

Junior Member

I have the following problem with the DELETEXML function :

my table called scl_profile content the following XML field :

select ID, profile_data from scl_profile
WHERE existsNode(profile_data,
'/exportImportProduitsMarcheCriteria/colonnesExport="LIBELLE_1"') = 1
and profile_xmltype =
'fr.mipih.marches.produitenmarche.criteres.ExportImportProduitsMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.DetailMarche.ExporterProduits.OptionsExportImport';

<?xml version="1.0" encoding="UTF-8" standalone='yes'?>
<exportImportProduitsMarcheCriteria>
<colonnesExport>CODE_MARCHE</colonnesExport>
<colonnesExport>NUM_LOT</colonnesExport>
<colonnesExport>CODE_FOURNISSEUR</colonnesExport>
<colonnesExport>PERIODE_DEBUT</colonnesExport>
<colonnesExport>PERIODE_FIN</colonnesExport>
<colonnesExport>PRODUIT</colonnesExport>
<colonnesExport>QUANTITE</colonnesExport>
<colonnesExport>QUANTITE_ECHANTILLONS</colonnesExport>
<colonnesExport>INDICATEUR_DE_PRIX_VARIABLE</colonnesExport>
<colonnesExport>HISTO_DATE_DEBUT</colonnesExport>
<colonnesExport>HISTO_DATE_FIN</colonnesExport>
<colonnesExport>PUMA</colonnesExport>
<colonnesExport>TVA</colonnesExport>
<colonnesExport>LIBELLE_1</colonnesExport>
<colonnesExport>LIBELLE_2</colonnesExport>
<colonnesExport>LIBELLE_3</colonnesExport>
<colonnesExport>REFERENCE</colonnesExport>
<colonnesExport>CONDITIONNEMENT</colonnesExport>
<colonnesExport>QUANTITE_CONDITIONNEMENT</colonnesExport>
<colonnesExport>NUM_PT_DE_CMD</colonnesExport>
<colonnesExport>DELAI_LIVRAISON</colonnesExport>
<colonnesExport>UNITE_DE_DELAI</colonnesExport>
<colonnesExport>REFERENCE_FABRICANT</colonnesExport>
<colonnesExport>BLOC_NOTE</colonnesExport>
<enregistreOptions>true</enregistreOptions>
<fermetureProduitAutreMarche>false</fermetureProduitAutreMarche>
<modeImport>ANNULE_ET_REMPLACE</modeImport>
<typePeriodeExport>TOUTE</typePeriodeExport>
</exportImportProduitsMarcheCriteria>

I want to delete item " <colonnesExport>LIBELLE_1</colonnesExport> " in this field.
So i run the following statement :

update scl_profile
set profile_data=deletexml(profile_data,'/exportImportProduitsMarcheCriteria[@colonnesExport="LIBELLE_1"]')
WHERE existsNode(profile_data,
'/exportImportProduitsMarcheCriteria/colonnesExport="LIBELLE_1"') = 1
and profile_xmltype =
'fr.mipih.marches.produitenmarche.criteres.ExportImportProduitsMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.DetailMarche.ExporterProduits.OptionsExportImport';

The item is not deleted.
What is the problem ? A syntax one ?
Thanks a lot for your help.

Report message to a moderator

Re: Using DELETEXML SQL function [message #585940 is a reply to message #585938]

Fri, 31 May 2013 03:45

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The syntax should be (without @ which indicates an attribute and not the value/text of the node):

deletexml(profile_data, '/exportImportProduitsMarcheCriteria[colonnesExport="LIBELLE_1"]')

But it returns an error "ORA-31013: Invalid XPATH expression" in 10.2.0.4 and NULL in 11.2.0.1 (so a bug for me).
I have not 11.2.0.3 for the moment, so can't test it.
Could you and send us the result?

Regards
Michel

Report message to a moderator

Re: Using DELETEXML SQL function [message #585941 is a reply to message #585940]

Fri, 31 May 2013 03:50

jelencik
Messages: 8
Registered: May 2013
Location: TOULOUSE

Junior Member

I try the syntax :

deletexml(profile_data, '/exportImportProduitsMarcheCriteria[colonnesExport="LIBELLE_1"]')

and i have the following error :
[Error] Execution (12: 8): ORA-01400: impossible d'insérer NULL dans ("DVCHT131"."SCL_PROFILE"."SYS_NC00004$")

Report message to a moderator

Re: Using DELETEXML SQL function [message #585971 is a reply to message #585941]

Fri, 31 May 2013 10:16

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please post a complete test case that generate the error so we can reproduce what you have.

Regards
Michel

Report message to a moderator

Re: Using DELETEXML SQL function [message #586024 is a reply to message #585940]

Sun, 02 June 2013 08:30

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Fri, 31 May 2013 04:45

The syntax should be (without @ which indicates an attribute and not the value/text of the node):
deletexml(profile_data, '/exportImportProduitsMarcheCriteria[colonnesExport="LIBELLE_1"]')

Not exactly. '/exportImportProduitsMarcheCriteria[colonnesExport="LIBELLE_1"]' means delete node exportImportProduitsMarcheCriteria if it has child node colonnesExport with text value of "LIBELLE_1" and therefore will delete whole xml:

SQL> select  *
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> with t as (
  2             select  1 id,xmltype('<?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
  3  <exportImportProduitsMarcheCriteria>
  4  <colonnesExport>CODE_MARCHE</colonnesExport>
  5  <colonnesExport>NUM_LOT</colonnesExport>
  6  <colonnesExport>CODE_FOURNISSEUR</colonnesExport>
  7  <colonnesExport>PERIODE_DEBUT</colonnesExport>
  8  <colonnesExport>PERIODE_FIN</colonnesExport>
  9  <colonnesExport>PRODUIT</colonnesExport>
 10  <colonnesExport>QUANTITE</colonnesExport>
 11  <colonnesExport>QUANTITE_ECHANTILLONS</colonnesExport>
 12  <colonnesExport>INDICATEUR_DE_PRIX_VARIABLE</colonnesExport>
 13  <colonnesExport>HISTO_DATE_DEBUT</colonnesExport>
 14  <colonnesExport>HISTO_DATE_FIN</colonnesExport>
 15  <colonnesExport>PUMA</colonnesExport>
 16  <colonnesExport>TVA</colonnesExport>
 17  <colonnesExport>LIBELLE_1</colonnesExport>
 18  <colonnesExport>LIBELLE_2</colonnesExport>
 19  <colonnesExport>LIBELLE_3</colonnesExport>
 20  <colonnesExport>REFERENCE</colonnesExport>
 21  <colonnesExport>CONDITIONNEMENT</colonnesExport>
 22  <colonnesExport>QUANTITE_CONDITIONNEMENT</colonnesExport>
 23  <colonnesExport>NUM_PT_DE_CMD</colonnesExport>
 24  <colonnesExport>DELAI_LIVRAISON</colonnesExport>
 25  <colonnesExport>UNITE_DE_DELAI</colonnesExport>
 26  <colonnesExport>REFERENCE_FABRICANT</colonnesExport>
 27  <colonnesExport>BLOC_NOTE</colonnesExport>
 28  <enregistreOptions>true</enregistreOptions>
 29  <fermetureProduitAutreMarche>false</fermetureProduitAutreMarche>
 30  <modeImport>ANNULE_ET_REMPLACE</modeImport>
 31  <typePeriodeExport>TOUTE</typePeriodeExport>
 32  </exportImportProduitsMarcheCriteria>') xmldoc from dual
 33            )
 34  select  id,
 35          deletexml(xmldoc,'/exportImportProduitsMarcheCriteria[colonnesExport="LIBELLE_1"]') xmldoc
 36    from  t
 37  /

        ID XMLDOC
---------- ----------------------------------------------------------------------
         1

SQL>

OP wants to delete node <colonnesExport>LIBELLE_1</colonnesExport>, therefore in XMLDELETE we need to use path '/exportImportProduitsMarcheCriteria/colonnesExport'. But there are multiple colonnesExport nodes under exportImportProduitsMarcheCriteria. We need to choose one where text is LIBELLE_1, so we can use '/exportImportProduitsMarcheCriteria/colonnesExport[.="LIBELLE_1"]' where dot is self-reference (I used XMLSERIALIZE to get xml "pretty" output):

with t as (
           select  1 id,xmltype('<?xml version="1.0" encoding="UTF-8" standalone=''yes''?>
<exportImportProduitsMarcheCriteria>
<colonnesExport>CODE_MARCHE</colonnesExport>
<colonnesExport>NUM_LOT</colonnesExport>
<colonnesExport>CODE_FOURNISSEUR</colonnesExport>
<colonnesExport>PERIODE_DEBUT</colonnesExport>
<colonnesExport>PERIODE_FIN</colonnesExport>
<colonnesExport>PRODUIT</colonnesExport>
<colonnesExport>QUANTITE</colonnesExport>
<colonnesExport>QUANTITE_ECHANTILLONS</colonnesExport>
<colonnesExport>INDICATEUR_DE_PRIX_VARIABLE</colonnesExport>
<colonnesExport>HISTO_DATE_DEBUT</colonnesExport>
<colonnesExport>HISTO_DATE_FIN</colonnesExport>
<colonnesExport>PUMA</colonnesExport>
<colonnesExport>TVA</colonnesExport>
<colonnesExport>LIBELLE_1</colonnesExport>
<colonnesExport>LIBELLE_2</colonnesExport>
<colonnesExport>LIBELLE_3</colonnesExport>
<colonnesExport>REFERENCE</colonnesExport>
<colonnesExport>CONDITIONNEMENT</colonnesExport>
<colonnesExport>QUANTITE_CONDITIONNEMENT</colonnesExport>
<colonnesExport>NUM_PT_DE_CMD</colonnesExport>
<colonnesExport>DELAI_LIVRAISON</colonnesExport>
<colonnesExport>UNITE_DE_DELAI</colonnesExport>
<colonnesExport>REFERENCE_FABRICANT</colonnesExport>
<colonnesExport>BLOC_NOTE</colonnesExport>
<enregistreOptions>true</enregistreOptions>
<fermetureProduitAutreMarche>false</fermetureProduitAutreMarche>
<modeImport>ANNULE_ET_REMPLACE</modeImport>
<typePeriodeExport>TOUTE</typePeriodeExport>
</exportImportProduitsMarcheCriteria>') xmldoc from dual
          )
select  id,
        xmlserialize(
                     content deletexml(xmldoc,'/exportImportProduitsMarcheCriteria/colonnesExport[.="LIBELLE_1"]')
                     indent size = 1
                    ) xmldoc
  from  t
/

        ID XMLDOC
---------- ----------------------------------------------------------------------
         1 <?xml version="1.0" encoding="WINDOWS-1252" standalone='yes'?>
           <exportImportProduitsMarcheCriteria>
            <colonnesExport>CODE_MARCHE</colonnesExport>
            <colonnesExport>NUM_LOT</colonnesExport>
            <colonnesExport>CODE_FOURNISSEUR</colonnesExport>
            <colonnesExport>PERIODE_DEBUT</colonnesExport>
            <colonnesExport>PERIODE_FIN</colonnesExport>
            <colonnesExport>PRODUIT</colonnesExport>
            <colonnesExport>QUANTITE</colonnesExport>
            <colonnesExport>QUANTITE_ECHANTILLONS</colonnesExport>
            <colonnesExport>INDICATEUR_DE_PRIX_VARIABLE</colonnesExport>

        ID XMLDOC
---------- ----------------------------------------------------------------------
            <colonnesExport>HISTO_DATE_DEBUT</colonnesExport>
            <colonnesExport>HISTO_DATE_FIN</colonnesExport>
            <colonnesExport>PUMA</colonnesExport>
            <colonnesExport>TVA</colonnesExport>
            <colonnesExport>LIBELLE_2</colonnesExport>
            <colonnesExport>LIBELLE_3</colonnesExport>
            <colonnesExport>REFERENCE</colonnesExport>
            <colonnesExport>CONDITIONNEMENT</colonnesExport>
            <colonnesExport>QUANTITE_CONDITIONNEMENT</colonnesExport>
            <colonnesExport>NUM_PT_DE_CMD</colonnesExport>
            <colonnesExport>DELAI_LIVRAISON</colonnesExport>

        ID XMLDOC
---------- ----------------------------------------------------------------------
            <colonnesExport>UNITE_DE_DELAI</colonnesExport>
            <colonnesExport>REFERENCE_FABRICANT</colonnesExport>
            <colonnesExport>BLOC_NOTE</colonnesExport>
            <enregistreOptions>true</enregistreOptions>
            <fermetureProduitAutreMarche>false</fermetureProduitAutreMarche>
            <modeImport>ANNULE_ET_REMPLACE</modeImport>
            <typePeriodeExport>TOUTE</typePeriodeExport>
           </exportImportProduitsMarcheCriteria>


SQL>

SY.

Report message to a moderator