This discussion is archived
14 Replies Latest reply: Feb 6, 2013 5:36 PM by 794743 RSS

Generate XML file with Elements and attributes from Oracle table

794743 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi AlexAnd,

    Thanks a lot..This is working good.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points