Oracle XML type parsing issue

Question

My XML File is stored in oracle column

<?xml version="1.0" encoding="UTF-8"?> <BasicProductTemplateType xmlns="http://www.asia.com/app/Product_2_0" xmlns:jBos_Common_1_0="http://www.asia.com/jBos/Common_1_0"> <jBos_Common_1_0:displayName> <jBos_Common_1_0:key>label</jBos_Common_1_0:key> </jBos_Common_1_0:displayName> <jBos_Common_1_0:description>label</jBos_Common_1_0:description> <jBos_Common_1_0:extensionProperty name="invoicingCoId" value="2"/> <jBos_Common_1_0:extensionProperty name="currencyCode" value="CNY"/> <jBos_Common_1_0:extensionProperty name="taxStatus" value="false"/> <productRatePlan> <name>Default</name> <selectionType>SELECTONE</selectionType> </productRatePlan> <productAttribute> <name>Baiying_attr_00</name> <displayName> <jBos_Common_1_0:key>Label</jBos_Common_1_0:key> </displayName> <type entityName="bmiasia.app.siulib.siu.common.StringSIU"/> <description> <jBos_Common_1_0:key>Label</jBos_Common_1_0:key> </description> <required>false</required> <source>INTERNAL</source> </productAttribute> </BasicProductTemplateType>

When I am trying to execute the below query i am not getting any result

SELECT XMLTYPE(XMLDATA).EXTRACT('//productAttribute/name/text()').getStringVal() FROM TABLE

What is wrong in my query

Stephen ODonnell · Accepted Answer · 2011-07-08 10:35:48Z

5

Welcome to the world of XMLs most annoy 'feature' - namespaces. As your XML document has namespaces defined in it, you need to specify the namespace of the nodes you are selecting. Note that there are two namespaces in your XML document, so you need to use the correct one. The following works as a PLSQL block for your XML:

set serveroutput on;

declare
  v_xml varchar2(32767) := '<?xml version="1.0" encoding="UTF-8"?> <BasicProductTemplateType xmlns="http://www.asia.com/app/Product_2_0" xmlns:jBos_Common_1_0="http://www.asia.com/jBos/Common_1_0"> <jBos_Common_1_0:displayName> <jBos_Common_1_0:key>label</jBos_Common_1_0:key> </jBos_Common_1_0:displayName> <jBos_Common_1_0:description>label</jBos_Common_1_0:description> <jBos_Common_1_0:extensionProperty name="invoicingCoId" value="2"/> <jBos_Common_1_0:extensionProperty name="currencyCode" value="CNY"/> <jBos_Common_1_0:extensionProperty name="taxStatus" value="false"/> <productRatePlan> <name>Default</name> <selectionType>SELECTONE</selectionType> </productRatePlan> <productAttribute> <name>Baiying_attr_00</name> <displayName> <jBos_Common_1_0:key>Label</jBos_Common_1_0:key> </displayName> <type entityName="bmiasia.app.siulib.siu.common.StringSIU"/> <description> <jBos_Common_1_0:key>Label</jBos_Common_1_0:key> </description> <required>false</required> <source>INTERNAL</source> </productAttribute> </BasicProductTemplateType>';
  v_xmltype xmltype;
begin
  v_xmltype := xmltype.createxml(v_xml);
  dbms_output.put_line(v_xmltype.EXTRACT('//productAttribute/name/text()', 'xmlns="http://www.asia.com/app/Product_2_0"').getStringVal());
end;
/

answered Jul 8, 2011 at 10:35

Stephen ODonnell

4,47619 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kiran Over a year ago

Yes, its working now.... If there are multiple nodes of product attribute then can i get the input in cursor and parse the data and more over we have multiple nodes in product attribute node and i want to bring data sample code is

<productAttribute>         <name>Baiying_attr_03</name>         <configParameter name="defaultValue" value="true"/>          <required>false</required>     </productAttribute>     <productAttribute>         <name>Baiying_attr_04</name>         <configParameter name="defaultValue" value="true"/>          <required>false</required>     </productAttribute>

Collectives™ on Stack Overflow

Oracle XML type parsing issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related