Results 1 to 9 of 9

Thread: XML from table

  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Question Unanswered: XML from table

    Hi!!!
    I'm trying to make a XML from a table, i've done almost everything but i haven´t found a way to get the XML version. I found a funcion: "SYS_XMLGen" but send me an error
    HTML Code:
    No authorized routine named "SYS_XMLGEN" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.11.69
    My code is:
    Code:
    SELECT XMLELEMENT(NAME "BIT_Bitacora_Out",
    	XMLELEMENT(NAME "Identificadores",
    		xmlelement(NAME "oficina", '20'),
    		xmlelement(NAME "medio_pago", '2'),
    		xmlelement(NAME "estado", '9'),
    		xmlelement(NAME "tipo_seguro", '1'),
    		xmlelement(NAME "anio_poliza", '1')),
    	XMLELEMENT(NAME "Contabilizar",
    		xmlelement(NAME "bco-cta", 'BANAMEX00605044'),
    		xmlelement(NAME "medio_pago", '2'),
    		xmlelement(NAME "fe_a_contabilizar", SUBSTR(FECMVTO,1,4)||'-'||SUBSTR(FECMVTO,5,2)||'-'||SUBSTR(FECMVTO,7,2)),
    		xmlelement(NAME "fe_ini_vigencia", SUBSTR(FECMVTO,1,4)||'-'||SUBSTR(FECMVTO,5,2)||'-'||SUBSTR(FECMVTO,7,2)),
    		XMLGROUP (ID_CEC, CVE_Subramo, IMP_CEC AS "importe", Ban_SIC AS "referencia" OPTION ROW "CEC" ROOT "Estructura")))
    from aetadeo.PASO_INFOPLANTILLA  WHERE ID_ODS_Evento = '1001' GROUP BY FECMVTO;
    Best regards

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Is it in DB2? What version,platform?

    By changing table and column names to reflect my test db, I get the output.

    =
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2011
    Posts
    57
    Is with DB2 verion 9.7, the function "SYS_XMLGEN" is for oracle and i'm looking for a function like this from DB2, the last query returns an XML bu doesn't contains the version:

    <?xml version="1.0" encoding="UTF-8"?>

    with the query only i get:

    <BIT_Bitacora_Out>
    <Identificadores>
    <oficina>20</oficina>
    <medio_pago>2</medio_pago>
    <estado>9</estado>
    <tipo_seguro>1</tipo_seguro>
    <anio_poliza>1</anio_poliza>
    </Identificadores>

    <Contabilizar>
    <bco-cta>BANAMEX00605044</bco-cta>
    <medio_pago>2</medio_pago>
    <fe_a_contabilizar>2012-01-25</fe_a_contabilizar>
    <fe_ini_vigencia>2012-01-25</fe_ini_vigencia>
    <Estructura>
    <CEC>
    <ID_CEC>2</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>212.25</importe>
    <referencia>0</referencia>
    </CEC>
    <CEC>
    <ID_CEC>3</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>.00</importe>
    <referencia>0</referencia>
    </CEC>
    <CEC>
    <ID_CEC>4</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>34.02</importe>
    <referencia>0</referencia>
    </CEC>
    <CEC>
    <ID_CEC>5</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>.06</importe>
    <referencia>0</referencia>
    </CEC>
    <CEC>
    <ID_CEC>7</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>.03</importe>
    <referencia>0</referencia>
    </CEC>
    <CEC>
    <ID_CEC>7</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>.03</importe>
    <referencia>0</referencia>
    </CEC>
    <CEC>
    <ID_CEC>1</ID_CEC>
    <CVE_SUBRAMO>034</CVE_SUBRAMO>
    <importe>246.33</importe>
    <referencia>0</referencia>
    </CEC>
    </Estructura>
    </Contabilizar>
    </BIT_Bitacora_Out>

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ah ok... I get your question now ..

    where are you running this from ?

    if in CLP, include -d

    db2 -tdvf <sqlquery>

    otherwise, post how you are retriving this - Java , .Net etc ????
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2011
    Posts
    57
    I'm usig data studio.

    Thanks

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    execute

    update command options using d on

    on your session and then do the SQL.

    ==
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Sep 2011
    Posts
    57
    Thanks a lot.

    Best regards.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As a sidenote, when generating XML , use XML objects and methods in your application to retrieve the generated XML. Eg, in JDBC , use getDB2XmlString() instead of getDB2String() method to include the XML declaration(<?xml version="1.0" encoding="UTF-8"?>).

    =
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Wrap the outermost XMLELEMENT() in an XMLDOCUMENT(). What you're getting now is an XML fragment, not a complete XML document.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •