6 Replies Latest reply: Sep 14, 2012 12:42 AM by Marwim RSS

    How to add version and encoding to XML

    Marwim
      Hello,

      I generate an XML using code like
      SELECT  XMLELEMENT(
                   "Document"
                  ,XMLAttributes(
                       'urn:iso:std:iso:20022:tech:xsd:pain.001.002.99' AS "xmlns"
                      ,'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"
                      ,'urn:iso:std:iso:20022:tech:xsd:pain.001.002.99 pain.001.002.99.xsd' AS "xsi:schemaLocation"
                      )
              )
      FROM    dual;
      The result ist
      <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.002.99"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.001.002.99 pain.001.002.99.xsd">
      </Document>
      But I need
      <?xml version="1.0" encoding="UTF-8"?>
      <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.002.99"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.001.002.99 pain.001.002.99.xsd">
      </Document>
      Can I add the header within my select?

      The XMLTYPE is inserted into a table and from there transfered via webutil to a client location.
      I found that I can convert the XMLTYPE to a DOM document and then use dbms_XMLDom.setCharset, but how can I convert it back to a XMLTYPE. The only way I found is to convert it to a CLOB and then into a XMLTYPE.

      Regards
      Marcus

      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
        • 1. Re: How to add version and encoding to XML
          Jason_(A_Non)
          I was going to suggest [url http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB5030]XMLRoot but it doesn't include the encoding and then I noticed
          ... is deprecated as a standard function as of SQL/XML 2005. It remains available in Oracle XML DB, as an Oracle SQL function.
          Usually this is done when writing the XML out to disk so I was going to suggest either [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions252.htm#SQLRF06231]XMLSerialize or something like {message:id=9873374}.

          I believe the main reason that the prolog is not stored with the XML is because of {thread:id=375391}, which basically says that Oracle can translate from the DB character set to the client character set so what you store may not be what Oracle produces, depending upon the client that asks. Is this something that would be better suited to the side that extracts the XML from the table your process stores it in?

          Finally, you also have [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xmldom.htm#i1124847]dbms_xmldom.getXMLType
          • 2. Re: How to add version and encoding to XML
            AlexAnd
            >
            I was going to suggest XMLRoot but it doesn't include the encoding
            >
            why? :)
            SELECT XMLROOT(  
                   XMLELEMENT(
                         "Document"
                        ,XMLAttributes(
                             'urn:iso:std:iso:20022:tech:xsd:pain.001.002.99' AS "xmlns"
                            ,'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"
                            ,'urn:iso:std:iso:20022:tech:xsd:pain.001.002.99 pain.001.002.99.xsd' AS "xsi:schemaLocation"
                            )
                    )
                    , version '1.0" encoding="UTF-8'
                   )
            FROM    dual;
            another way
            SELECT XMLTYPE
                       (
                          '<?xml version="1.0" encoding="UTF-8"?>' ||
                          xmlElement("Foo",'Fii').getClobVal()
                       )
            FROM DUAL
            • 3. Re: How to add version and encoding to XML
              Marwim
              Usually this is done when writing the XML out to disk
              Yes, every soulution I found here suggested this. But the XML is transfered using webutil_file_transfer.db_to_client_with_progress. This means, that I cannot apply any method, it is written to the client machine without further processing.

              Regards
              Marcus
              • 4. Re: How to add version and encoding to XML
                Marwim
                SELECT XMLROOT(  
                ...>         , version '1.0" encoding="UTF-8'
                )
                FROM    dual;
                Thanks, that's it.
                >
                I was going to suggest XMLRoot but it doesn't include the encoding
                >
                why? :)
                Since the docu does not say that this is a valid argument, how am I supposed to find this?

                Regards
                Marcus

                Edited by: Marwim on 07.09.2012 11:54

                Well, if I think about it: version simply adds the argument within double quotes, whatever I supply, even when the argument contains double quotes itself.
                • 5. Re: How to add version and encoding to XML
                  odie_63
                  Yes, every soulution I found here suggested this. But the XML is transfered using webutil_file_transfer.db_to_client_with_progress.
                  If you're transferring the file as a BLOB then you can use XMLSerialize() function with the ENCODING option. It provides a cleaner way than the XMLRoot "hack" and also ensures the required encoding is used.
                  • 6. Re: How to add version and encoding to XML
                    Marwim
                    Hello Marc,

                    thanks for the answer. I should have added this as a follow up. I generate my BLOB with
                    SELECT  XMLSERIALIZE(
                                DOCUMENT <xml_column>
                                AS BLOB
                                ENCODING 'UTF-8'
                                VERSION '1.0'
                                --INDENT SIZE = 1  -- uncomment to make output more readable
                                )
                    FROM    <xml_table>
                    I found this when I tried to generate a human readable output for debugging and digged deeper into the possible arguments for XMLSERIALIZE.

                    Ragards
                    Marcus