8 Replies Latest reply: Mar 5, 2013 10:28 PM by 794743 RSS

    Retrieve XML Attributes data in a SQL Query

    794743
      Hi All,

      Oracle & XML versions are as follows

      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
      PL/SQL Release 10.2.0.3.0 - Production
      CORE 10.2.0.3.0 Production

      Oracle XML Database 10.2.0.3.0

      The XSD file is as follows

      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true" xdb:schemaURL="http://www.acel.com/COIL.xsd" xdb:schemaOwner="APPS" xdb:numProps="23">
      <xs:element name="Coil" type="CoilType" xdb:defaultTable="XXTIF_EDI_COIL" xdb:columnProps="CONSTRAINT coil_pk PRIMARY KEY (XMLDATA.COIL_ID)" xdb:tableProps="VARRAY XMLDATA.TESTS STORE AS TABLE XXTIF_EDI_COIL_TESTS ((CONSTRAINT action_pkey PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))" xdb:propNumber="3258" xdb:global="true" xdb:SQLName="Coil" xdb:SQLType="COIL_T" xdb:SQLSchema="APPS" xdb:memType="258" xdb:defaultTableSchema="APPS"/>
      <xs:complexType name="CoilType" xdb:SQLType="COIL_T" xdb:SQLSchema="APPS">
      <xs:sequence>
      <xs:element name="CoilId" xdb:SQLName="COIL_ID" xdb:propNumber="3259" xdb:global="false" xdb:SQLType="NUMBER" xdb:memType="2" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:positiveInteger">
      <xs:totalDigits value="10"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="CoilNo" xdb:SQLName="COIL_NUMBER" xdb:propNumber="3260" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="40"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="CoilPOOperatingUnit" xdb:SQLName="COIL_PO_OPERATING_UNIT" xdb:propNumber="3261" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="20"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="CoilPONo" xdb:SQLName="COIL_PO_NUMBER" xdb:propNumber="3262" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="40"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="MillNo" xdb:SQLName="MILL_NUMBER" xdb:propNumber="3263" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:length value="2"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="MillCoilStatus" xdb:SQLName="MILL_COIL_STATUS" xdb:propNumber="3264" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="15"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="ItemNo" xdb:SQLName="ITEM_NUMBER" xdb:propNumber="3265" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="40"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="Weight" xdb:SQLName="COIL_WEIGHT" xdb:propNumber="3266" xdb:global="false" xdb:SQLType="NUMBER" xdb:memType="2" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:positiveInteger"/>
      </xs:simpleType>
      </xs:element>
      <xs:element name="UOM" xdb:SQLName="UOM" xdb:propNumber="3267" xdb:global="false" xdb:SQLType="XDB$ENUM_T" xdb:SQLSchema="XDB" xdb:memType="259" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="10"/>
      <xs:enumeration value="POUNDS"/>
      <xs:enumeration value="KILOGRAM"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="DocumentNo" xdb:SQLName="DOCUMENT_NUMBER" xdb:propNumber="3268" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="40"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="DocumentDate" type="xs:date" xdb:SQLName="DOCUMENT_DATE" xdb:propNumber="3269" xdb:global="false" xdb:SQLType="DATE" xdb:memType="12" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true"/>
      <xs:element name="DocumentStatus" xdb:SQLName="DOCUMENT_STATUS" xdb:propNumber="3270" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="15"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="DocumentType" xdb:SQLName="DOCUMENT_TYPE" xdb:propNumber="3271" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="20"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="DocumentSource" xdb:SQLName="DOCUMENT_SOURCE" xdb:propNumber="3272" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1" xdb:SQLInline="true" xdb:MemInline="true" xdb:JavaInline="true">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="20"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
      <xs:element name="Tests" maxOccurs="100" xdb:SQLName="TESTS" xdb:propNumber="3280" xdb:global="false" xdb:SQLType="TESTS_T" xdb:SQLSchema="APPS" xdb:memType="258" xdb:SQLInline="true" xdb:MemInline="false" xdb:JavaInline="false" xdb:SQLCollType="TESTS330_COLL" xdb:SQLCollSchema="APPS">
      <xs:complexType xdb:SQLType="TESTS_T" xdb:SQLSchema="APPS">
      <xs:attribute name="TestId" xdb:SQLName="TEST_ID" xdb:propNumber="3273" xdb:global="false" xdb:SQLType="NUMBER" xdb:memType="2">
      <xs:simpleType>
      <xs:restriction base="xs:positiveInteger"/>
      </xs:simpleType>
      </xs:attribute>
      <xs:attribute name="Value" xdb:SQLName="TEST_VALUE" xdb:propNumber="3274" xdb:global="false" xdb:SQLType="NUMBER" xdb:memType="2">
      <xs:simpleType>
      <xs:restriction base="xs:decimal"/>
      </xs:simpleType>
      </xs:attribute>
      <xs:attribute name="TestUnit" xdb:SQLName="TEST_UNIT" use="optional" xdb:propNumber="3275" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="20"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:attribute>
      <xs:attribute name="Status" xdb:SQLName="STATUS" xdb:propNumber="3276" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="70"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:attribute>
      <xs:attribute name="TesterLogin" xdb:SQLName="TESTER_LOGIN" use="optional" xdb:propNumber="3277" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="20"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:attribute>
      <xs:attribute name="EquipmentCode" xdb:SQLName="EQUIPMENT_CODE" use="optional" xdb:propNumber="3278" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="50"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:attribute>
      <xs:attribute name="DocStsMsg" xdb:SQLName="DOC_STS_MSG" use="optional" xdb:propNumber="3279" xdb:global="false" xdb:SQLType="VARCHAR2" xdb:memType="1">
      <xs:simpleType>
      <xs:restriction base="xs:string">
      <xs:maxLength value="600"/>
      </xs:restriction>
      </xs:simpleType>
      </xs:attribute>
      </xs:complexType>
      </xs:element>
      </xs:sequence>
      </xs:complexType>
      </xs:schema>

      The sample XML file is as follows

      <?xml version="1.0"?>
      <Coil xsi:noNamespaceSchemaLocation="www.acel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb">
      <CoilId>6889532</CoilId>
      <CoilNo>12221050010A0</CoilNo>
      <CoilPOOperatingUnit>Harare</CoilPOOperatingUnit>
      <CoilPONo>445619</CoilPONo>
      <MillNo>8621</MillNo>
      <MillCoilStatus>Test</MillCoilStatus>
      <ItemNo>050FP8003 C5</ItemNo>
      <Weight>7076</Weight>
      <UOM>KILOGRAM</UOM>
      <DocumentNo>0</DocumentNo>
      <DocumentDate>2010-11-25</DocumentDate>
      <DocumentStatus>NonProc</DocumentStatus>
      <DocumentType>Tests</DocumentType>
      <DocumentSource>HARARE1</DocumentSource>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4992" TestUnit="mm" Status="NonProc" TestId="135"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.0128" TestUnit="mm" Status="NonProc" TestId="124"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="12" TestUnit="mm" Status="NonProc" TestId="125"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5095" TestUnit="mm" Status="NonProc" TestId="127"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5042" TestUnit="mm" Status="NonProc" TestId="128"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5058" TestUnit="mm" Status="NonProc" TestId="129"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4967" TestUnit="mm" Status="NonProc" TestId="130"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5049" TestUnit="mm" Status="NonProc" TestId="131"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4972" TestUnit="mm" Status="NonProc" TestId="132"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4960" TestUnit="mm" Status="NonProc" TestId="133"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="sammen" Value="0.4996" TestUnit="mm" Status="NonProc" TestId="134"/>
      </Coil>

      The table creations script is as follows

      CREATE TABLE CO_TAB (XML_DATA_DOC XMLTYPE,
                          CREATE_DATE DATE DEFAULT SYSDATE NOT NULL,
      CONSTRAINT coil_pk PRIMARY KEY (XMLDOC."XMLDATA".COIL_ID)
      )
      XMLTYPE COLUMN XMLDOC STORE AS OBJECT RELATIONAL
      XMLSCHEMA "http://www.acel.com/COIL.xsd" ELEMENT "Coil"
      /

      DECLARE

      V_XML     XMLTYPE := '<?xml version="1.0"?>
      <Coil xsi:noNamespaceSchemaLocation="www.acel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb">
      <CoilId>6889532</CoilId>
      <CoilNo>12221050010A0</CoilNo>
      <CoilPOOperatingUnit>Harare</CoilPOOperatingUnit>
      <CoilPONo>445619</CoilPONo>
      <MillNo>8621</MillNo>
      <MillCoilStatus>Test</MillCoilStatus>
      <ItemNo>050FP8003 C5</ItemNo>
      <Weight>7076</Weight>
      <UOM>KILOGRAM</UOM>
      <DocumentNo>0</DocumentNo>
      <DocumentDate>2010-11-25</DocumentDate>
      <DocumentStatus>NonProc</DocumentStatus>
      <DocumentType>Tests</DocumentType>
      <DocumentSource>HARARE1</DocumentSource>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4992" TestUnit="mm" Status="NonProc" TestId="135"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.0128" TestUnit="mm" Status="NonProc" TestId="124"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="12" TestUnit="mm" Status="NonProc" TestId="125"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5095" TestUnit="mm" Status="NonProc" TestId="127"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5042" TestUnit="mm" Status="NonProc" TestId="128"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5058" TestUnit="mm" Status="NonProc" TestId="129"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4967" TestUnit="mm" Status="NonProc" TestId="130"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5049" TestUnit="mm" Status="NonProc" TestId="131"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4972" TestUnit="mm" Status="NonProc" TestId="132"/>
      <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4960" TestUnit="mm" Status="NonProc" TestId="133"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="sammen" Value="0.4996" TestUnit="mm" Status="NonProc" TestId="134"/>
      </Coil>';

      BEGIN

      INSERT INTO CO_TAB (XML_DATA_DOC) VALUES (V_XML);

      COMMIT;

      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('THE ERROR '||SQLERRM);
      END;
      /

      Now, When,I used the following SQL,It is working fine until I start adding attributes elements such as DocStsMsg/EquipmentCode/Testlogin/data.

      select extractValue(e.XML_DATA_DOC,'/Coil/CoilId')                    AS COIL_ID,
      extractValue(e.XML_DATA_DOC,'/Coil/CoilNo')      AS COIL_NUMBER,
      extractValue(e.XML_DATA_DOC,'/Coil/CoilPOOperatingUnit')     AS COIL_PO_OPERATING_UNIT,
      extractValue(e.XML_DATA_DOC,'/Coil/CoilPONo')      AS COIL_PO_NUMBER,
      extractValue(e.XML_DATA_DOC,'/Coil/MillNo')      AS MILL_NUMBER,
      extractValue(e.XML_DATA_DOC,'/Coil/MillCoilStatus')      AS MILL_COIL_STATUS,
      extractValue(e.XML_DATA_DOC,'/Coil/ItemNo')      AS ITEM_NUMBER,
      extractValue(e.XML_DATA_DOC,'/Coil/Weight')      AS COIL_WEIGHT,
      extractValue(e.XML_DATA_DOC,'/Coil/UOM')      AS WEIGHT_UOM,
      extractValue(e.XML_DATA_DOC,'/Coil/DocumentNo')      AS DOCUMENT_NUMBER,
      extractValue(e.XML_DATA_DOC,'/Coil/DocumentDate')      AS DOCUMENT_DATE,
      extractValue(e.XML_DATA_DOC,'/Coil/DocumentStatus')      AS DOCUMENT_STATUS,
      extractValue(e.XML_DATA_DOC,'/Coil/DocumentType')      AS DOCUMENT_TYPE,
      extractValue(e.XML_DATA_DOC,'/Coil/DocumentSource')      AS DOCUMENT_SOURCE,
      e.CREATE_DATE
      from CO_TAB e
      /

      What is the best way of adding attributes column in SQL?
      Please suggest.

      Thanks in Advance.
        • 2. Re: Retrieve XML Attributes data in a SQL Query
          odie_63
          What is the best way of adding attributes column in SQL?
          It seems I already gave you an example of that in your previous thread : {message:id=10881777}

          Did you try to adapt it to your real data ?
          • 3. Re: Retrieve XML Attributes data in a SQL Query
            794743
            Hi Alex/Odie,

            When I ran the below query,

            SELECT x.*,e.create_date
            FROM CO_TAB e,
            XMLTable( '/Coil' passing e.XML_DATA_DOC
                      columns     COIL_ID                NUMBER(10)      path '@CoilId',
                                COIL_NUMBER                    VARCHAR2(40)     PATH '@CoilNo',
                                COIL_PO_OPERATING_UNIT     VARCHAR2(20)     PATH '@CoilPOOperatingUnit'
                 ) x
            WHERE ROWNUM < 2;

            The output is as follows

            COIL_ID COIL_NUMBER COIL_PO_OPERATING_UN          CREATE_DA
            ---------- ---------------------------------------- --------------------          ---------
                                                                                                                02-MAR-13


            1 row selected.

            Elapsed: 00:00:00.02

            The result I got was null values for COIL_ID,COIL_NUMBER and COIL_PO_OPERATING_UNIT. But got value only for Create_Date.

            When I ran the below query,

            SELECT x.*,e.create_date
            FROM CO_TAB e,
            XMLTable( '/Coil' passing e.XML_DATA_DOC
                      columns     COIL_ID                NUMBER(10)      path '@CoilId',
                                COIL_NUMBER                    VARCHAR2(40)     PATH '@CoilNo',
                                COIL_PO_OPERATING_UNIT     VARCHAR2(20)     PATH '@CoilPOOperatingUnit',
                                TEST_ID                         NUMBER(38)          PATH 'Tests/@TestId',
                                          VALUE                         NUMBER               PATH 'Tests/@Value',
                                          TEST_UOM                    VARCHAR2(20)     PATH 'Tests/@TestUnit'
                 ) x
            WHERE ROWNUM < 5;

            The error is as follows

            SELECT x.*,e.create_date
            *
            ERROR at line 1:
            ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

            Please let me know, what are the error for both the queries?

            Thanks in advance.
            • 4. Re: Retrieve XML Attributes data in a SQL Query
              AlexAnd
              why you can not try to change odie_63's example from Re: How to create default column in XML schema ? ?
              >
              SQL>
              SQL> select x."EmpNo", h.*
              2 from employees e
              3 , xmltable('/Emp' passing e.xmldoc
              4 columns "EmpNo" number(10)
              5 , "WorkHrs" xmltype
              6 ) x
              7 left outer join
              8 xmltable('/WorkHrs' passing x."WorkHrs"
              9 columns work_day varchar2(20) path '@Day'
              10 , work_hrs number path '@Hrs'
              11 ) h
              12 on 1 = 1
              >

              so for your case
               31  select x.CoilId, y.DocStsMsg, y.TestId
               32    from t,
               33         xmltable('/Coil' passing t.xml
               34                  columns CoilId path 'CoilId',
               35                          tests xmltype path '.') x,
               36         xmltable('/Coil/Tests' passing x.tests
               37                  columns DocStsMsg varchar2(20) path '@DocStsMsg',
               38                          TestId varchar2(20) path '@TestId') y
               39  /
               
              COILID                                                                           DOCSTSMSG            TESTID
              -------------------------------------------------------------------------------- -------------------- --------------------
              6889532                                                                          0                    135
              6889532                                                                          0                    124
              6889532                                                                          0                    125
              6889532                                                                          0                    127
              6889532                                                                          0                    128
              6889532                                                                          0                    129
              6889532                                                                          0                    130
              6889532                                                                          0                    131
              6889532                                                                          0                    132
              6889532                                                                          0                    133
              6889532                                                                          0                    134
               
              11 rows selected
               
              SQL> 
              • 5. Re: Retrieve XML Attributes data in a SQL Query
                794743
                Hi Alex,

                Thanks for the solution. It worked really well..
                All I missed was "TESTS XMLTYPE PATH '.'" in the query and that was troubling.

                Also,would like to know, how to improve the performance of such a query, When I changed the SQL according to my needs and started executing, It didn't return the expected result.
                The query is as follows

                SELECT COIL_PO_OPERATING_UNIT,COUNT(1)
                FROM
                (
                     SELECT X.COIL_ID                AS COIL_ID,
                          X.COIL_NUMBER          AS COIL_NUMBER,
                          X.COIL_PO_OPERATING_UNIT      AS COIL_PO_OPERATING_UNIT,
                          X.COIL_PO_NUMBER          AS COIL_PO_NUMBER,
                          X.MILL_NUMBER          AS MILL_NUMBER,
                          X.MILL_COIL_STATUS          AS MILL_COIL_STATUS,
                          X.ITEM_NUMBER          AS ITEM_NUMBER,
                          X.COIL_WEIGHT          AS COIL_WEIGHT,
                          X.WEIGHT_UOM               AS WEIGHT_UOM,
                          X.DOCUMENT_NUMBER          AS DOCUMENT_NUMBER,
                          X.DOCUMENT_DATE AS DOCUMENT_DATE,
                          X.DOCUMENT_STATUS AS DOCUMENT_STATUS,
                          X.DOCUMENT_TYPE AS DOCUMENT_TYPE,
                          X.DOCUMENT_SOURCE AS DOCUMENT_SOURCE,
                          Y.TEST_ID               AS TEST_ID,
                          Y.VALUE               AS VALUE,               
                          Y.TEST_UOM          AS TEST_UOM,          
                          Y.TEST_STATUS          AS TEST_STATUS,          
                          Y.TESTER_LOGIN     AS TESTER_LOGIN,     
                          Y.EQUIPMENT_CODE     AS EQUIPMENT_CODE,     
                          Y.DOC_STS_MSG      AS DOC_STS_MSG,
                CO.CREATE_DATE      AS CREATE_DATE
                FROM CO_TAB CO,
                XMLTABLE('/Coil' PASSING CO.XML_DATA_DOC
                COLUMNS COIL_ID           NUMBER(10) PATH 'CoilId',
                          COIL_NUMBER      VARCHAR2(40) PATH 'CoilNo',
                          COIL_PO_OPERATING_UNIT      VARCHAR2(20) PATH 'CoilPOOperatingUnit',
                          COIL_PO_NUMBER      VARCHAR2(40) PATH 'CoilPONo',
                          MILL_NUMBER      VARCHAR2(2) PATH 'MillNo',
                          MILL_COIL_STATUS      VARCHAR2(15) PATH 'MillCoilStatus',
                          ITEM_NUMBER      VARCHAR2(40) PATH 'ItemNo',
                          COIL_WEIGHT      NUMBER(38) PATH 'Weight',
                          WEIGHT_UOM           VARCHAR2(10) PATH 'UOM',
                          DOCUMENT_NUMBER      VARCHAR2(40) PATH 'DocumentNo',
                          DOCUMENT_DATE      DATE PATH 'DocumentDate',
                          DOCUMENT_STATUS      VARCHAR2(15) PATH 'DocumentStatus',
                          DOCUMENT_TYPE      VARCHAR2(20) PATH 'DocumentType',
                          DOCUMENT_SOURCE      VARCHAR2(20) PATH 'DocumentSource',
                TESTS XMLTYPE PATH '.'
                     ) X,
                XMLTABLE('/Coil/Tests' PASSING X.TESTS
                COLUMNS TEST_ID      NUMBER(38)      PATH '@TestId',
                          VALUE      NUMBER      PATH '@Value',
                          TEST_UOM      VARCHAR2(20)      PATH '@TestUnit',
                          TEST_STATUS VARCHAR2(70)      PATH '@Status',
                          TESTER_LOGIN      VARCHAR2(20)      PATH '@TesterLogin',
                          EQUIPMENT_CODE VARCHAR2(50)      PATH '@EquipmentCode',
                          DOC_STS_MSG VARCHAR2(600)      PATH '@DocStsMsg'
                ) Y
                )
                GROUP BY COIL_PO_OPERATING_UNIT
                /

                The count of records in that table, could be around million, to start with.
                Please let me know for any techinques.

                Thanks in Advance.
                • 6. Re: Retrieve XML Attributes data in a SQL Query
                  odie_63
                  Hi,

                  Sorry to be blunt but you don't seem to have paid attention to what I already explained in your previous thread.

                  Let me try to detail it again, with some additional comments :

                  - Do not provide the internal registered version of the schema, it could lead to errors if we try to use it on our side.

                  - "xdb:defaultTable", "xdb:storeVarrayAsTable", "xdb:columnProps" and "xdb:tableProps" are useless since you're ultimately creating a custom table. Those annotations are only taken into account when using the defaultTable option.

                  - There's a mismatch between the URL of the registered schema ("http://www.acel.com/COIL.xsd") and the location in the XML document (www.acel.com/COIL.xsd).
                  - Declaring the "xdb" prefix in the instance document is also completely useless.

                  - The table DDL is not correct : XMLType column is named XML_DATA_DOC, and becomes XMLDOC afterwards...

                  - The provided XML sample is not valid : as per the schema <MillNo> element is a string of size 2, but the instance document contains "8621"


                  It's really not easy to help with that much inconsistencies.
                  • 7. Re: Retrieve XML Attributes data in a SQL Query
                    odie_63
                    Most likely the performance issue comes from the fact that the nested structure (i.e. the collection of <Tests> elements) is not actually stored in a nested table, therefore Oracle cannot perform a full query rewrite using the SQL data model.

                    Demonstration on the following test case :
                    begin
                     
                      dbms_xmlschema.registerSchema(
                        schemaURL => 'http://www.acel.com/COIL.xsd'
                      , schemaDoc => xmltype(bfilename('XML_DIR','coil.xsd'), nls_charset_id('AL32UTF8'))
                      , local => true
                      , genTypes => true
                      , genTables => false
                      , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                      ) ;
                    
                    end;
                    /
                    
                    CREATE TABLE co_tab (
                      xml_data_doc XMLTYPE
                    , create_date DATE DEFAULT SYSDATE NOT NULL
                    , CONSTRAINT coil_pk PRIMARY KEY (xml_data_doc."XMLDATA".COIL_ID)
                    )
                    XMLTYPE COLUMN xml_data_doc STORE AS OBJECT RELATIONAL
                    XMLSCHEMA "http://www.acel.com/COIL.xsd" ELEMENT "Coil" ;
                    
                    insert into co_tab (xml_data_doc)  values (
                    xmltype('<?xml version="1.0"?>
                    <Coil xsi:noNamespaceSchemaLocation="http://www.acel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                    <CoilId>6889532</CoilId>
                    <CoilNo>12221050010A0</CoilNo>
                    <CoilPOOperatingUnit>Harare</CoilPOOperatingUnit>
                    <CoilPONo>445619</CoilPONo>
                    <MillNo>86</MillNo>
                    <MillCoilStatus>Test</MillCoilStatus>
                    <ItemNo>050FP8003 C5</ItemNo>
                    <Weight>7076</Weight>
                    <UOM>KILOGRAM</UOM>
                    <DocumentNo>0</DocumentNo>
                    <DocumentDate>2010-11-25</DocumentDate>
                    <DocumentStatus>NonProc</DocumentStatus>
                    <DocumentType>Tests</DocumentType>
                    <DocumentSource>HARARE1</DocumentSource>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4992" TestUnit="mm" Status="NonProc" TestId="135"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.0128" TestUnit="mm" Status="NonProc" TestId="124"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="12" TestUnit="mm" Status="NonProc" TestId="125"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5095" TestUnit="mm" Status="NonProc" TestId="127"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5042" TestUnit="mm" Status="NonProc" TestId="128"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5058" TestUnit="mm" Status="NonProc" TestId="129"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4967" TestUnit="mm" Status="NonProc" TestId="130"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5049" TestUnit="mm" Status="NonProc" TestId="131"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4972" TestUnit="mm" Status="NonProc" TestId="132"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4960" TestUnit="mm" Status="NonProc" TestId="133"/>
                    <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="sammen" Value="0.4996" TestUnit="mm" Status="NonProc" TestId="134"/>
                    </Coil>')
                    );
                    
                    exec dbms_stats.gather_table_stats(user, 'CO_TAB');
                    See the COLLECTION ITERATOR operation below, it indicates that, for each row, Oracle casts the VARRAY column supporting the collection to a "virtual" table and fetch from it.
                    This can become very slow as the number of rows in the main table increases.
                    SQL> SELECT X.COIL_ID                   AS COIL_ID,
                      2         X.COIL_NUMBER               AS COIL_NUMBER,
                      3         X.COIL_PO_OPERATING_UNIT    AS COIL_PO_OPERATING_UNIT,
                      4         X.COIL_PO_NUMBER            AS COIL_PO_NUMBER,
                      5         X.MILL_NUMBER               AS MILL_NUMBER,
                      6         X.MILL_COIL_STATUS          AS MILL_COIL_STATUS,
                      7         X.ITEM_NUMBER               AS ITEM_NUMBER,
                      8         X.COIL_WEIGHT               AS COIL_WEIGHT,
                      9         X.WEIGHT_UOM                        AS WEIGHT_UOM,
                     10         X.DOCUMENT_NUMBER           AS DOCUMENT_NUMBER,
                     11         X.DOCUMENT_DATE              AS DOCUMENT_DATE,
                     12         X.DOCUMENT_STATUS            AS DOCUMENT_STATUS,
                     13         X.DOCUMENT_TYPE              AS DOCUMENT_TYPE,
                     14         X.DOCUMENT_SOURCE            AS DOCUMENT_SOURCE,
                     15         Y.TEST_ID                   AS TEST_ID,
                     16         Y.VALUE                     AS VALUE,
                     17         Y.TEST_UOM                  AS TEST_UOM,
                     18         Y.TEST_STATUS               AS TEST_STATUS,
                     19         Y.TESTER_LOGIN              AS TESTER_LOGIN,
                     20         Y.EQUIPMENT_CODE            AS EQUIPMENT_CODE,
                     21         Y.DOC_STS_MSG               AS DOC_STS_MSG,
                     22         CO.CREATE_DATE              AS CREATE_DATE
                     23           FROM CO_TAB CO,
                     24                XMLTABLE('/Coil' PASSING CO.XML_DATA_DOC
                     25                          COLUMNS COIL_ID                    NUMBER(10)    PATH 'CoilId',
                     26                                  COIL_NUMBER                VARCHAR2(40)  PATH 'CoilNo',
                     27                                  COIL_PO_OPERATING_UNIT     VARCHAR2(20)  PATH 'CoilPOOperatingUnit',
                     28                                  COIL_PO_NUMBER             VARCHAR2(40)  PATH 'CoilPONo',
                     29                                  MILL_NUMBER                VARCHAR2(2)   PATH 'MillNo',
                     30                                  MILL_COIL_STATUS           VARCHAR2(15)  PATH 'MillCoilStatus',
                     31                                  ITEM_NUMBER                VARCHAR2(40)  PATH 'ItemNo',
                     32                                  COIL_WEIGHT                NUMBER(38)    PATH 'Weight',
                     33                                  WEIGHT_UOM                 VARCHAR2(10)  PATH 'UOM',
                     34                                  DOCUMENT_NUMBER            VARCHAR2(40)  PATH 'DocumentNo',
                     35                                  DOCUMENT_DATE              DATE          PATH 'DocumentDate',
                     36                                  DOCUMENT_STATUS            VARCHAR2(15)  PATH 'DocumentStatus',
                     37                                  DOCUMENT_TYPE              VARCHAR2(20)  PATH 'DocumentType',
                     38                                  DOCUMENT_SOURCE            VARCHAR2(20)  PATH 'DocumentSource',
                     39                    TESTS                 XMLTYPE PATH 'Tests'
                     40                       ) X,
                     41                XMLTABLE('/Tests' PASSING X.TESTS
                     42                          COLUMNS TEST_ID            NUMBER(38)      PATH '@TestId',
                     43                                  VALUE              NUMBER          PATH '@Value',
                     44                                  TEST_UOM           VARCHAR2(20)    PATH '@TestUnit',
                     45                                  TEST_STATUS        VARCHAR2(70)    PATH '@Status',
                     46                                  TESTER_LOGIN       VARCHAR2(20)    PATH '@TesterLogin',
                     47                                  EQUIPMENT_CODE     VARCHAR2(50)    PATH '@EquipmentCode',
                     48                                  DOC_STS_MSG        VARCHAR2(600)   PATH '@DocStsMsg'
                     49                  ) Y ;
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 1289742728
                    
                    ----------------------------------------------------------------------------------------------
                    | Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                    ----------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                    |        |   818 | 85890 |     8  (13)| 00:00:01 |
                    |   1 |  MERGE JOIN CARTESIAN               |        |   818 | 85890 |     8  (13)| 00:00:01 |
                    |*  2 |   TABLE ACCESS FULL                 | CO_TAB |     1 |   103 |     2   (0)| 00:00:01 |
                    |   3 |   BUFFER SORT                       |        |   818 |  1636 |     6  (17)| 00:00:01 |
                    |*  4 |    COLLECTION ITERATOR PICKLER FETCH|        |       |       |            |          |
                    ----------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       2 - filter("CO"."SYS_NC00006$" IS NOT NULL)
                       4 - filter(VALUE(KOKBF$) IS NOT NULL)
                    The solution is to instruct Oracle to store the VARRAY in a NESTED TABLE explicitly in the CREATE TABLE statement, just like you were trying to do with the xdb:tableProps annotation in the schema.
                    But as already explained, since it's driven by the defaultTable option, the annotation was ignored.
                    DROP TABLE co_tab PURGE;
                    
                    CREATE TABLE co_tab (
                      xml_data_doc XMLTYPE
                    , create_date DATE DEFAULT SYSDATE NOT NULL
                    , CONSTRAINT coil_pk PRIMARY KEY (xml_data_doc."XMLDATA".COIL_ID)
                    )
                    XMLTYPE COLUMN xml_data_doc STORE AS OBJECT RELATIONAL
                    XMLSCHEMA "http://www.acel.com/COIL.xsd" ELEMENT "Coil"
                    VARRAY xml_data_doc."XMLDATA".TESTS STORE AS TABLE co_tab_coil_tests (
                     ( CONSTRAINT co_tab_coil_tests_pk PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$) )
                    ) ;
                    
                    insert into co_tab (xml_data_doc)  values (
                    xmltype('<?xml version="1.0"?>
                    <Coil xsi:noNamespaceSchemaLocation="http://www.acel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                    <CoilId>6889532</CoilId>
                    <CoilNo>12221050010A0</CoilNo>
                    <CoilPOOperatingUnit>Harare</CoilPOOperatingUnit>
                    <CoilPONo>445619</CoilPONo>
                    <MillNo>86</MillNo>
                    <MillCoilStatus>Test</MillCoilStatus>
                    <ItemNo>050FP8003 C5</ItemNo>
                    <Weight>7076</Weight>
                    <UOM>KILOGRAM</UOM>
                    <DocumentNo>0</DocumentNo>
                    <DocumentDate>2010-11-25</DocumentDate>
                    <DocumentStatus>NonProc</DocumentStatus>
                    <DocumentType>Tests</DocumentType>
                    <DocumentSource>HARARE1</DocumentSource>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4992" TestUnit="mm" Status="NonProc" TestId="135"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.0128" TestUnit="mm" Status="NonProc" TestId="124"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="12" TestUnit="mm" Status="NonProc" TestId="125"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5095" TestUnit="mm" Status="NonProc" TestId="127"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5042" TestUnit="mm" Status="NonProc" TestId="128"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5058" TestUnit="mm" Status="NonProc" TestId="129"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4967" TestUnit="mm" Status="NonProc" TestId="130"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.5049" TestUnit="mm" Status="NonProc" TestId="131"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4972" TestUnit="mm" Status="NonProc" TestId="132"/>
                    <Tests DocStsMsg="0" EquipmentCode="HARARE1" TesterLogin="sammen" Value="0.4960" TestUnit="mm" Status="NonProc" TestId="133"/>
                    <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="sammen" Value="0.4996" TestUnit="mm" Status="NonProc" TestId="134"/>
                    </Coil>')
                    );
                    
                    exec dbms_stats.gather_table_stats(user, 'CO_TAB');
                    Now, the query is fully rewritten and should be considerably faster :
                    SQL> SELECT X.COIL_ID                   AS COIL_ID,
                      2         X.COIL_NUMBER               AS COIL_NUMBER,
                      3         X.COIL_PO_OPERATING_UNIT    AS COIL_PO_OPERATING_UNIT,
                      4         X.COIL_PO_NUMBER            AS COIL_PO_NUMBER,
                      5         X.MILL_NUMBER               AS MILL_NUMBER,
                      6         X.MILL_COIL_STATUS          AS MILL_COIL_STATUS,
                      7         X.ITEM_NUMBER               AS ITEM_NUMBER,
                      8         X.COIL_WEIGHT               AS COIL_WEIGHT,
                      9         X.WEIGHT_UOM                        AS WEIGHT_UOM,
                     10         X.DOCUMENT_NUMBER           AS DOCUMENT_NUMBER,
                     11         X.DOCUMENT_DATE              AS DOCUMENT_DATE,
                     12         X.DOCUMENT_STATUS            AS DOCUMENT_STATUS,
                     13         X.DOCUMENT_TYPE              AS DOCUMENT_TYPE,
                     14         X.DOCUMENT_SOURCE            AS DOCUMENT_SOURCE,
                     15         Y.TEST_ID                   AS TEST_ID,
                     16         Y.VALUE                     AS VALUE,
                     17         Y.TEST_UOM                  AS TEST_UOM,
                     18         Y.TEST_STATUS               AS TEST_STATUS,
                     19         Y.TESTER_LOGIN              AS TESTER_LOGIN,
                     20         Y.EQUIPMENT_CODE            AS EQUIPMENT_CODE,
                     21         Y.DOC_STS_MSG               AS DOC_STS_MSG,
                     22         CO.CREATE_DATE              AS CREATE_DATE
                     23           FROM CO_TAB CO,
                     24                XMLTABLE('/Coil' PASSING CO.XML_DATA_DOC
                     25                          COLUMNS COIL_ID                    NUMBER(10)    PATH 'CoilId',
                     26                                  COIL_NUMBER                VARCHAR2(40)  PATH 'CoilNo',
                     27                                  COIL_PO_OPERATING_UNIT     VARCHAR2(20)  PATH 'CoilPOOperatingUnit',
                     28                                  COIL_PO_NUMBER             VARCHAR2(40)  PATH 'CoilPONo',
                     29                                  MILL_NUMBER                VARCHAR2(2)   PATH 'MillNo',
                     30                                  MILL_COIL_STATUS           VARCHAR2(15)  PATH 'MillCoilStatus',
                     31                                  ITEM_NUMBER                VARCHAR2(40)  PATH 'ItemNo',
                     32                                  COIL_WEIGHT                NUMBER(38)    PATH 'Weight',
                     33                                  WEIGHT_UOM                 VARCHAR2(10)  PATH 'UOM',
                     34                                  DOCUMENT_NUMBER            VARCHAR2(40)  PATH 'DocumentNo',
                     35                                  DOCUMENT_DATE              DATE          PATH 'DocumentDate',
                     36                                  DOCUMENT_STATUS            VARCHAR2(15)  PATH 'DocumentStatus',
                     37                                  DOCUMENT_TYPE              VARCHAR2(20)  PATH 'DocumentType',
                     38                                  DOCUMENT_SOURCE            VARCHAR2(20)  PATH 'DocumentSource',
                     39                    TESTS                 XMLTYPE PATH 'Tests'
                     40                       ) X,
                     41                XMLTABLE('/Tests' PASSING X.TESTS
                     42                          COLUMNS TEST_ID            NUMBER(38)      PATH '@TestId',
                     43                                  VALUE              NUMBER          PATH '@Value',
                     44                                  TEST_UOM           VARCHAR2(20)    PATH '@TestUnit',
                     45                                  TEST_STATUS        VARCHAR2(70)    PATH '@Status',
                     46                                  TESTER_LOGIN       VARCHAR2(20)    PATH '@TesterLogin',
                     47                                  EQUIPMENT_CODE     VARCHAR2(50)    PATH '@EquipmentCode',
                     48                                  DOC_STS_MSG        VARCHAR2(600)   PATH '@DocStsMsg'
                     49                  ) Y ;
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 1236569680
                    
                    ----------------------------------------------------------------------------------------
                    | Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
                    ----------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT   |                   |    11 |  6006 |     5  (20)| 00:00:01 |
                    |*  1 |  HASH JOIN         |                   |    11 |  6006 |     5  (20)| 00:00:01 |
                    |*  2 |   TABLE ACCESS FULL| CO_TAB            |     1 |   120 |     2   (0)| 00:00:01 |
                    |   3 |   TABLE ACCESS FULL| CO_TAB_COIL_TESTS |    11 |  4686 |     2   (0)| 00:00:01 |
                    ----------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       1 - access("SYS_ALIAS_1"."NESTED_TABLE_ID"="CO"."SYS_NC0002200023$")
                       2 - filter("CO"."SYS_NC00006$" IS NOT NULL)
                    Another query, after inserting another XML document with a different CoilId :
                    SQL> insert into co_tab (xml_data_doc)  values (
                      2  xmltype('<?xml version="1.0"?>
                      3  <Coil xsi:noNamespaceSchemaLocation="http://www.acel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                      4  <CoilId>6889533</CoilId>
                      5  <CoilNo>12221050010A0</CoilNo>
                      6  <CoilPOOperatingUnit>Harare2</CoilPOOperatingUnit>
                      7  <CoilPONo>445619</CoilPONo>
                      8  <MillNo>86</MillNo>
                      9  <MillCoilStatus>Test</MillCoilStatus>
                     10  <ItemNo>050FP8003 C5</ItemNo>
                     11  <Weight>7076</Weight>
                     12  <UOM>KILOGRAM</UOM>
                     13  <DocumentNo>0</DocumentNo>
                     14  <DocumentDate>2010-11-25</DocumentDate>
                     15  <DocumentStatus>NonProc</DocumentStatus>
                     16  <DocumentType>Tests</DocumentType>
                     17  <DocumentSource>HARARE1</DocumentSource>
                     18  <Tests DocStsMsg="0" EquipmentCode="HARARE2" TesterLogin="sammen" Value="0.4992" TestUnit="mm" Status="NonProc" TestId="135"/>
                     19  <Tests DocStsMsg="0" EquipmentCode="HARARE2" TesterLogin="sammen" Value="0.0128" TestUnit="mm" Status="NonProc" TestId="124"/>
                     20  </Coil>')
                     21  );
                    
                    1 row created.
                    
                    SQL> SELECT x.coil_po_operating_unit
                      2       , count(*)
                      3  FROM co_tab co
                      4     , XMLTable('/Coil'
                      5         passing CO.XML_DATA_DOC
                      6         columns coil_po_operating_unit VARCHAR2(20) PATH 'CoilPOOperatingUnit'
                      7               , tests                  XMLTYPE      PATH 'Tests'
                      8       ) x
                      9     , XMLTABLE('/Tests' PASSING x.tests)
                     10  GROUP BY x.coil_po_operating_unit ;
                    
                    COIL_PO_OPERATING_UN   COUNT(*)
                    -------------------- ----------
                    Harare2                       2
                    Harare                       11
                    • 8. Re: Retrieve XML Attributes data in a SQL Query
                      794743
                      Hi Odie,

                      I Thank you very much for the solution offered and also very clear explanation.
                      I noticed after running the query, performance was better than my previous SQL/table structure.

                      Thank You Once again..