14 Replies Latest reply: Feb 6, 2013 7:36 PM by 794743 RSS

    Generate XML file with Elements and attributes from Oracle table

    794743
      Hi,

      I have the following table structure.

      CREATE TABLE COIL
      (
      COIL_ID                         NUMBER(10),
      COIL_NUMBER                    VARCHAR2(40),
      COIL_PO_OPERATING_UNIT     VARCHAR2(20),
      COIL_PO_NUMBER               VARCHAR2(40),
      MILL_NUMBER                    VARCHAR2(2),
      MILL_COIL_STATUS          VARCHAR2(15),
      ITEM_NUMBER                    VARCHAR2(40),
      COIL_WEIGHT                    NUMBER(38),
      WEIGHT_UOM                    VARCHAR2(10),
      DOCUMENT_NUMBER               VARCHAR2(40),
      DOCUMENT_DATE               DATE,
      DOCUMENT_STATUS               VARCHAR2(15),
      DOCUMENT_TYPE               VARCHAR2(20),
      DOCUMENT_SOURCE               VARCHAR2(20),
      TEST_ID                         NUMBER(38),
      VALUE                         NUMBER,
      TEST_UOM                    VARCHAR2(20),
      TEST_STATUS               VARCHAR2(70),
      TESTER_LOGIN               VARCHAR2(20),
      EQUIPMENT_CODE               VARCHAR2(50),
      DOC_STS_MSG               VARCHAR2(600)
      )
      /
      For each COILID record, there could be multiple records baased on TEST_ID/VALUE/TEST_UOM etc.

      And I would like to prepare xml file in the following format by selecting data from COIL?


      <?xml version="1.0"?>
      -<Coil xsi:noNamespaceSchemaLocation="www.tempel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb">
      <CoilId>1419532</CoilId>
      <CoilNo>D2221050010A0</CoilNo>
      <CoilPOOperatingUnit>Changzhou</CoilPOOperatingUnit>
      <CoilPONo>4619</CoilPONo>
      <MillNo>86</MillNo>
      <MillCoilStatus>Test</MillCoilStatus>
      <ItemNo>050FP800 C5</ItemNo>
      <Weight>7076</Weight>
      <UOM>KILOGRAM</UOM>
      <DocumentNo>0</DocumentNo>
      <DocumentDate>2013-01-11</DocumentDate>
      <DocumentStatus>NonProcessed</DocumentStatus>
      <DocumentType>Tests</DocumentType>
      <DocumentSource>CHIGMA1</DocumentSource>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.4992" TestUnit="mm" Status="NonProcessed" TestId="135"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.0128" TestUnit="mm" Status="NonProcessed" TestId="124"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="12" TestUnit="mm" Status="NonProcessed" TestId="125"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.5095" TestUnit="mm" Status="NonProcessed" TestId="127"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.5042" TestUnit="mm" Status="NonProcessed" TestId="128"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.5058" TestUnit="mm" Status="NonProcessed" TestId="129"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.4967" TestUnit="mm" Status="NonProcessed" TestId="130"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.5049" TestUnit="mm" Status="NonProcessed" TestId="131"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.4972" TestUnit="mm" Status="NonProcessed" TestId="132"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.4960" TestUnit="mm" Status="NonProcessed" TestId="133"/>
      <Tests DocStsMsg="0" EquipmentCode="CHIGMA1" TesterLogin="dpkrueger" Value="0.4996" TestUnit="mm" Status="NonProcessed" TestId="134"/>
      </Coil>

      Can you please guide me how to do it in a single query?

      Thanks in advance.
        • 1. Re: Generate XML file with Elements and attributes from Oracle table
          AlexAnd
          select appendchildxml(
                                xmltype('<Coil xsi:noNamespaceSchemaLocation="www.tempel.com/COIL.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb"></Coil>')
                                , 'Coil'
                                , xmlconcat(
                                            xmlelement("CoilId", COIL_ID)
                                            -- .....   
                                            , xmlelement("Tests", xmlattributes(DOC_STS_MSG as DocStsMsg,
                                                                              TESTER_LOGIN as TesterLogin)
                                                      )
                                             -- .....         
                                           )
                               )
            from coil
          • 2. Re: Generate XML file with Elements and attributes from Oracle table
            odie_63
            Sounds like a job for a GROUP-BY query with XMLAgg aggregate function :
            SELECT XMLElement("Coil",
                     XMLAttributes(
                       'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                     , 'www.tempel.com/COIL.xsd' as "xsi:noNamespaceSchemaLocation"
                     )
                   , XMLForest(
                     , COIL_ID                as "CoilId"
                     , COIL_NUMBER            as "CoilNo"
                     , COIL_PO_OPERATING_UNIT as "CoilPOOperatingUnit"
                     , COIL_PO_NUMBER         as "CoilPONo"
                     , MILL_NUMBER            as "MillNo"
                     , MILL_COIL_STATUS       as "MillCoilStatus"
                     , ITEM_NUMBER            as "ItemNo"
                     , COIL_WEIGHT            as "Weight"
                     , WEIGHT_UOM             as "UOM"
                     , DOCUMENT_NUMBER        as "DocumentNo"
                     , DOCUMENT_DATE          as "DocumentDate"
                     , DOCUMENT_STATUS        as "DocumentStatus"
                     , DOCUMENT_TYPE          as "DocumentType"
                     , DOCUMENT_SOURCE        as "DocumentSource"
                     )
                   , XMLAgg(
                       XMLElement("Tests",
                         XMLAttributes(
                           DOC_STS_MSG    as "DocStsMsg"
                         , EQUIPMENT_CODE as "EquipmentCode"
                         , TESTER_LOGIN   as "TesterLogin" 
                         , VALUE          as "Value"
                         , TEST_UOM       as "TestUnit"
                         , TEST_STATUS    as "Status"
                         , TEST_ID        as "TestId"
                         )
                       )
                     )
                   )
            FROM coil
            GROUP BY COIL_ID
                   , COIL_NUMBER
                   , COIL_PO_OPERATING_UNIT
                   , COIL_PO_NUMBER
                   , MILL_NUMBER
                   , MILL_COIL_STATUS
                   , ITEM_NUMBER
                   , COIL_WEIGHT
                   , WEIGHT_UOM
                   , DOCUMENT_NUMBER
                   , DOCUMENT_DATE
                   , DOCUMENT_STATUS
                   , DOCUMENT_TYPE
                   , DOCUMENT_SOURCE ;
            Note : XMLForest doesn't create element if the value is NULL. If it's an issue replace the necessary column(s) with XMLElement.
            • 3. Re: Generate XML file with Elements and attributes from Oracle table
              794743
              Hi AlexAnd/Odie,

              Thanks for the quick solution.They are very helpful.

              Odie.

              When I ran your suggested query,the format was not as expected.
              It end up in following way, where I am expecting something which I raised in my initial question.

              <Coil xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespace
              SchemaLocation="www.tempel.com/COIL.xsd"><CoilId>97239</CoilId><CoilNo>777078</CoilNo
              <CoilPOOperatingUnit>TSUSA</CoilPOOperatingUnit><CoilPONo>3407</CoilPONo><MillN
              o>31</MillNo><MillCoilStatus>Test</MillCoilStatus><ItemNo>0140SP150 C5A</ItemNo>
              <Weight>17365</Weight><UOM>POUNDS</UOM><DocumentNo>0</DocumentNo><DocumentDate>2
              008-10-13</DocumentDate><DocumentStatus>Processed</DocumentStatus><DocumentType>
              Tests</DocumentType><DocumentSource>MILL</DocumentSource><Tests EquipmentCode="M
              ILLEDI" TesterLogin="MILLEDI" Value="84" TestUnit="15T" Status="Processed" TestI
              d="65"></Tests></Coil>

              Am I missing something? Please let me know.

              Thanks in advance.
              • 4. Re: Generate XML file with Elements and attributes from Oracle table
                odie_63
                When I ran your suggested query,the format was not as expected.
                It end up in following way, where I am expecting something which I raised in my initial question.
                Give us some sample data (INSERTs) so that we can test our solutions.
                Ideally the data that's supposed to give your first sample XML.

                What's wrong with the output format? please explain.
                • 5. Re: Generate XML file with Elements and attributes from Oracle table
                  794743
                  Hi Odie,

                  Thanks for the quick offer.Sure,No problem.I expect the format to be as follows

                  <Coil xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="www.tempel.com/COIL.xsd">
                  <CoilId>97239</CoilId>
                  <CoilNo>777078</CoilNo>
                  <CoilPOOperatingUnit>TSUSA</CoilPOOperatingUnit>
                  <CoilPONo>3407</CoilPONo>
                  <MillNo>31</MillNo>
                  <MillCoilStatus>Test</MillCoilStatus>
                  <ItemNo>0140SP150 C5A</ItemNo>
                  <Weight>17365</Weight>
                  <UOM>POUNDS</UOM>
                  <DocumentNo>0</DocumentNo>
                  <DocumentDate>2008-10-13</DocumentDate>
                  <DocumentStatus>Processed</DocumentStatus>
                  <DocumentType>Tests</DocumentType>
                  <DocumentSource>MILL</DocumentSource>
                  <Tests EquipmentCode="MILLEDI" TesterLogin="MILLEDI" Value="84" TestUnit="15T" Status="Processed" TestId="65"></Tests>
                  <Tests EquipmentCode="MILLEDI" TesterLogin="MILLEDI" Value="1.39" TestUnit="W/Lb" Status="Processed" TestId="48"></Tests>
                  <Tests EquipmentCode="MILLEDI" TesterLogin="MILLEDI" Value="1979" TestUnit="W/Lb" Status="Processed" TestId="49"></Tests>
                  </Coil>

                  But instead it came with below format

                  <Coil xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSch
                  emaLocation="www.tempel.com/COIL.xsd"><CoilId>97239</CoilId><CoilNo>777078</Co
                  ilNo><CoilPOOperatingUnit>TSUSA</CoilPOOperatingUnit><CoilPONo>3407</CoilPONo>
                  <MillNo>31</MillNo><MillCoilStatus>Test</MillCoilStatus><ItemNo>0140SP150 C5A<
                  /ItemNo><Weight>17365</Weight><UOM>POUNDS</UOM><DocumentNo>0</DocumentNo><Docu
                  mentDate>2008-10-13</DocumentDate><DocumentStatus>Processed</DocumentStatus><Do
                  cumentType>Tests</DocumentType><DocumentSource>MILL</DocumentSource><Tests Equ
                  ipmentCode="MILLEDI" TesterLogin="MILLEDI" Value="84" TestUnit="15T" Status="P
                  rocessed" TestId="65"></Tests><Tests EquipmentCode="MILLEDI" TesterLogin="MILL
                  EDI" Value="1.39" TestUnit="W/Lb" St atus="Processed" TestId="48"></Tests><Tes
                  ts EquipmentCode="MILLEDI" TesterLog in="MILLEDI" Value="1979" TestUnit="W/Lb"
                  Status="Processed" TestId="49"></Tests></Coil>

                  The sample insert records are as follows

                  insert into COIL (coil_id, coil_number, coil_po_operating_unit, coil_po_number, mill_number, mill_coil_status, item_number, coil_weight, weight_uom, document_number, document_date, document_status, document_type, document_source, test_id, value, test_uom, test_status, tester_login, equipment_code, doc_sts_msg)
                  values (97239, '777078', 'USA', '3407', '31', 'Test', '0140SP150 C5A', 17365, 'POUNDS', '0', to_date('13-10-2008', 'dd-mm-yyyy'), 'Processed', 'Tests', 'MILL', 65, 84, '15T', 'Processed', 'MILLEDI', 'MILLEDI', null);
                  insert into COIL (coil_id, coil_number, coil_po_operating_unit, coil_po_number, mill_number, mill_coil_status, item_number, coil_weight, weight_uom, document_number, document_date, document_status, document_type, document_source, test_id, value, test_uom, test_status, tester_login, equipment_code, doc_sts_msg)
                  values (97239, '777078', 'USA', '3407', '31', 'Test', '0140SP150 C5A', 17365, 'POUNDS', '0', to_date('13-10-2008', 'dd-mm-yyyy'), 'Processed', 'Tests', 'MILL', 48, 1.39, 'W/Lb', 'Processed', 'MILLEDI', 'MILLEDI', null);
                  insert into COIL (coil_id, coil_number, coil_po_operating_unit, coil_po_number, mill_number, mill_coil_status, item_number, coil_weight, weight_uom, document_number, document_date, document_status, document_type, document_source, test_id, value, test_uom, test_status, tester_login, equipment_code, doc_sts_msg)
                  values (97239, '777078', 'USA', '3407', '31', 'Test', '0140SP150 C5A', 17365, 'POUNDS', '0', to_date('13-10-2008', 'dd-mm-yyyy'), 'Processed', 'Tests', 'MILL', 49, 1979, 'W/Lb', 'Processed', 'MILLEDI', 'MILLEDI', null);
                  commit;

                  Thanks in Advance.
                  • 6. Re: Generate XML file with Elements and attributes from Oracle table
                    odie_63
                    OK, I see.

                    I thought you meant the generated XML structure was wrong.
                    There's no semantic difference between the two "formats".

                    The format you want uses more insignificant whitespaces that are just there to increase the size of the document.
                    Indentations and newlines are not necessary to have a wellformed content.
                    If you want to check the content of the XML in a user-friendly way, use an XML editor (most web browsers can open XML and pretty-print it).

                    That being said, the XMLSerialize function does what you want :
                    SELECT XMLSerialize(document
                             XMLElement("Coil",
                               XMLAttributes(
                                 'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                               , 'www.tempel.com/COIL.xsd' as "xsi:noNamespaceSchemaLocation"
                               )
                             , XMLForest(
                                 COIL_ID                as "CoilId"
                               , COIL_NUMBER            as "CoilNo"
                               , COIL_PO_OPERATING_UNIT as "CoilPOOperatingUnit"
                               , COIL_PO_NUMBER         as "CoilPONo"
                               , MILL_NUMBER            as "MillNo"
                               , MILL_COIL_STATUS       as "MillCoilStatus"
                               , ITEM_NUMBER            as "ItemNo"
                               , COIL_WEIGHT            as "Weight"
                               , WEIGHT_UOM             as "UOM"
                               , DOCUMENT_NUMBER        as "DocumentNo"
                               , DOCUMENT_DATE          as "DocumentDate"
                               , DOCUMENT_STATUS        as "DocumentStatus"
                               , DOCUMENT_TYPE          as "DocumentType"
                               , DOCUMENT_SOURCE        as "DocumentSource"
                               )
                             , XMLAgg(
                                 XMLElement("Tests",
                                   XMLAttributes(
                                     DOC_STS_MSG    as "DocStsMsg"
                                   , EQUIPMENT_CODE as "EquipmentCode"
                                   , TESTER_LOGIN   as "TesterLogin" 
                                   , VALUE          as "Value"
                                   , TEST_UOM       as "TestUnit"
                                   , TEST_STATUS    as "Status"
                                   , TEST_ID        as "TestId"
                                   )
                                 )
                               )
                             )
                             indent
                           )
                    FROM coil
                    GROUP BY COIL_ID
                           , COIL_NUMBER
                           , COIL_PO_OPERATING_UNIT
                           , COIL_PO_NUMBER
                           , MILL_NUMBER
                           , MILL_COIL_STATUS
                           , ITEM_NUMBER
                           , COIL_WEIGHT
                           , WEIGHT_UOM
                           , DOCUMENT_NUMBER
                           , DOCUMENT_DATE
                           , DOCUMENT_STATUS
                           , DOCUMENT_TYPE
                           , DOCUMENT_SOURCE ;
                    • 7. Re: Generate XML file with Elements and attributes from Oracle table
                      794743
                      Hi Odie,

                      Thanks for the quick response.

                      When I tired to execute , it throws the following error for the line INDENT

                      "ERROR at line 37:
                      ORA-00907: missing right parenthesis"

                      Am I missing something?
                      Please let me know.
                      • 8. Re: Generate XML file with Elements and attributes from Oracle table
                        odie_63
                        Give your database version.
                        XMLSerialize (with INDENT) works on 11.1 and upwards.

                        What do you think about my previous comment?
                        For what reason do you need pretty-print?
                        • 9. Re: Generate XML file with Elements and attributes from Oracle table
                          794743
                          Hi Odie,

                          The database version is

                          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

                          As said by you,that's why it did not work in 10g.

                          Thanks again..
                          • 10. Re: Generate XML file with Elements and attributes from Oracle table
                            odie_63
                            EXTRACT function will force a pretty-print in your version :
                            SELECT XMLElement("Coil",
                                     XMLAttributes(
                                       'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                                     , 'www.tempel.com/COIL.xsd' as "xsi:noNamespaceSchemaLocation"
                                     )
                                   , XMLForest(
                                       COIL_ID                as "CoilId"
                                     , COIL_NUMBER            as "CoilNo"
                                     , COIL_PO_OPERATING_UNIT as "CoilPOOperatingUnit"
                                     , COIL_PO_NUMBER         as "CoilPONo"
                                     , MILL_NUMBER            as "MillNo"
                                     , MILL_COIL_STATUS       as "MillCoilStatus"
                                     , ITEM_NUMBER            as "ItemNo"
                                     , COIL_WEIGHT            as "Weight"
                                     , WEIGHT_UOM             as "UOM"
                                     , DOCUMENT_NUMBER        as "DocumentNo"
                                     , DOCUMENT_DATE          as "DocumentDate"
                                     , DOCUMENT_STATUS        as "DocumentStatus"
                                     , DOCUMENT_TYPE          as "DocumentType"
                                     , DOCUMENT_SOURCE        as "DocumentSource"
                                     )
                                   , XMLAgg(
                                       XMLElement("Tests",
                                         XMLAttributes(
                                           DOC_STS_MSG    as "DocStsMsg"
                                         , EQUIPMENT_CODE as "EquipmentCode"
                                         , TESTER_LOGIN   as "TesterLogin" 
                                         , VALUE          as "Value"
                                         , TEST_UOM       as "TestUnit"
                                         , TEST_STATUS    as "Status"
                                         , TEST_ID        as "TestId"
                                         )
                                       )
                                     )
                                   ).extract('/*')
                            FROM coil
                            GROUP BY COIL_ID
                                   , COIL_NUMBER
                                   , COIL_PO_OPERATING_UNIT
                                   , COIL_PO_NUMBER
                                   , MILL_NUMBER
                                   , MILL_COIL_STATUS
                                   , ITEM_NUMBER
                                   , COIL_WEIGHT
                                   , WEIGHT_UOM
                                   , DOCUMENT_NUMBER
                                   , DOCUMENT_DATE
                                   , DOCUMENT_STATUS
                                   , DOCUMENT_TYPE
                                   , DOCUMENT_SOURCE ;
                            • 11. Re: Generate XML file with Elements and attributes from Oracle table
                              794743
                              Hi Odie,

                              This is really helpful and got the expected Results.
                              Thanks a lot again..

                              Regards
                              • 12. Re: Generate XML file with Elements and attributes from Oracle table
                                794743
                                Hi Odie,

                                One last favor to check the below SQL. I have added xml version(line 1) in the following manner.
                                I ran the SQL and got expected output.
                                Can you Please suggest whether this is good or any other suggestions are highly welcome.

                                SELECT '<?xml version="1.0"?>',
                                XMLElement("Coil",
                                XMLAttributes(
                                'www.tempel.com/COIL.xsd' AS "xsi:noNamespaceSchemaLocation",
                                'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
                                'http://xmlns.oracle.com/xdb' AS "xmlns:xdb"
                                )
                                , XMLForest(
                                COIL_ID as "CoilId"
                                , COIL_NUMBER as "CoilNo"
                                , COIL_PO_OPERATING_UNIT as "CoilPOOperatingUnit"
                                , COIL_PO_NUMBER as "CoilPONo"
                                , MILL_NUMBER as "MillNo"
                                , MILL_COIL_STATUS as "MillCoilStatus"
                                , ITEM_NUMBER as "ItemNo"
                                , COIL_WEIGHT as "Weight"
                                , WEIGHT_UOM as "UOM"
                                , DOCUMENT_NUMBER as "DocumentNo"
                                , DOCUMENT_DATE as "DocumentDate"
                                , DOCUMENT_STATUS as "DocumentStatus"
                                , DOCUMENT_TYPE as "DocumentType"
                                , DOCUMENT_SOURCE as "DocumentSource"
                                )
                                , XMLAgg(
                                XMLElement("Tests",
                                XMLAttributes(
                                DOC_STS_MSG as "DocStsMsg"
                                , EQUIPMENT_CODE as "EquipmentCode"
                                , TESTER_LOGIN as "TesterLogin"
                                , VALUE as "Value"
                                , TEST_UOM as "TestUnit"
                                , TEST_STATUS as "Status"
                                , TEST_ID as "TestId"
                                )
                                )
                                )
                                ).extract('/*')
                                FROM COIL
                                WHERE COIL_ID = 97239
                                GROUP BY COIL_ID
                                , COIL_NUMBER
                                , COIL_PO_OPERATING_UNIT
                                , COIL_PO_NUMBER
                                , MILL_NUMBER
                                , MILL_COIL_STATUS
                                , ITEM_NUMBER
                                , COIL_WEIGHT
                                , WEIGHT_UOM
                                , DOCUMENT_NUMBER
                                , DOCUMENT_DATE
                                , DOCUMENT_STATUS
                                , DOCUMENT_TYPE
                                , DOCUMENT_SOURCE ;

                                Thanks in advance.
                                • 13. Re: Generate XML file with Elements and attributes from Oracle table
                                  AlexAnd
                                  XMLROOT - http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions225.htm
                                  SELECT XMLROOT ( XMLType('<poid>143598</poid>'), VERSION '1.0')
                                     AS "XMLROOT" FROM DUAL;
                                  • 14. Re: Generate XML file with Elements and attributes from Oracle table
                                    794743
                                    Hi AlexAnd,

                                    Thanks a lot..This is working good.