12 Replies Latest reply: Jun 4, 2012 2:15 PM by 939605 RSS

    extracting xmltype and excluding All namespace tags?

    939605
      Hi all,
      I am trying to extract xmltype column into another xmltype column in a different table.

      select xt.attachment_content, xt.attachment_type, xt.attachment_format
      FROM document_version dva,
      XMLTABLE (
      XMLNamespaces('http://oracle.com/test1/1.0'),
      'AttachmentList/Attachment'

      PASSING document_content
      COLUMNS
      attachment_content xmltype path 'content',
      attachment_type varchar2(100) path 'type',
      attachment_format varchar2(100) path 'format') xt

      This query returns results BUT content has namespaces and tags in the result set too. I just want it to return the contents only NO xml version no xmlns: (xmlnamespaces ) and etc.

      Please help me how to I do that.
        • 1. Re: extracting xmltype and excluding All namespace tags?
          Jason_(A_Non)
          A short answer is that you will need to apply XSL to transform the contents of attachment_content into XML that has no namespaces.

          select XMLTransform(xt.attachment_content, l_xsl) ....

          where l_xsl is a PL/SQL XMLType of the desired stylesheet.

          That should give you a start for now.
          • 2. Re: extracting xmltype and excluding All namespace tags?
            odie_63
            Hi,

            Post a sample XML and the result you're expecting. Thanks.
            • 3. Re: extracting xmltype and excluding All namespace tags?
              939605
              Please find the actual xml content below.

              <AttachmentList xmlns="http://optimal.com/SP/2/RandPCommon/DocumentStore/GatewayRequestStore/1.0" xmlns:ns0="http://optimal.com/SP/2/RandPCommon/DocumentStore/GatewayRequestStore/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <attachment>
              <content>&lt;?xml version="1.0" encoding="UTF-8"?>
              &lt;Results xmlns="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/3.100"
              xmlns:ns6="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Messages/3.100"
              xmlns:ns5="http://optimal.com/smti/sharedservices/Compliance/Analysis/ValidatorEvents/3.100"
              xmlns:ns3="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Summaries/3.100">
              &lt;Header proposalID="USA:AF:AS:fe0a0921268547d" complianceID="92304">
              &lt;Summary compliant="true" jobActSerialID="USA:AF:JA:cfef3d61d93745b"
              userSerialID="USA:AF:CN:4606deaf8ccb473" status="COMPLETE"
              requestedBy="FORWARD" updated="2012-04-03T08:02:40.485000Z"
              created="2012-04-03T08:02:40.220000Z" overallCls="U">
              &lt;ns3:Events infCount="0" rspCount="0" wrnCount="6" errCount="0">6&lt;/ns3:Events>
              &lt;ns3:Justifications missing="0" rspCount="0" wrnCount="6">6&lt;/ns3:Justifications>
              &lt;/Summary>
              &lt;/Header>
              &lt;ResultEvent eventID="K17" serial="USA:AF:LT:4341b33adc0442a" type="COMPLIANCE"
              level="WARNING" code="XTX0025" source="NTIA-XSL">
              &lt;ns5:Node serial="USA:AF:LT:4341b33adc0442a">
              &lt;ns5:epath>/1/2/28/8&lt;/ns5:epath>
              &lt;ns5:xpath>/SSRF/Body/Location[@serial='USA:AF:LT:4341b33adc0442a']/Address[1]/@country&lt;/ns5:xpath>
              &lt;ns5:values refValueName="country" refValueType="a">
              &lt;ns5:element name="Address"/>
              &lt;ns5:attribute name="cityArea">Brownsville&lt;/ns5:attribute>
              &lt;ns5:attribute name="cls">U&lt;/ns5:attribute>
              &lt;ns5:attribute name="country">TX&lt;/ns5:attribute>
              &lt;/ns5:values>
              &lt;/ns5:Node>
              &lt;ns5:Node serial="USA:AF:LT:7c5c3cef2cf74ef">
              &lt;ns5:epath>/1/2/27/8&lt;/ns5:epath>
              &lt;ns5:xpath>/SSRF/Body/Location[@serial='USA:AF:LT:7c5c3cef2cf74ef']/Address[1]/@country&lt;/ns5:xpath>
              &lt;ns5:values refValueName="country" refValueType="a">
              &lt;ns5:element name="Address"/>
              &lt;ns5:attribute name="cityArea">Brownsville&lt;/ns5:attribute>
              &lt;ns5:attribute name="cls">U&lt;/ns5:attribute>

              &lt;MessageText>
              &lt;ns6:Short>There is no successfully completed Engineering Analysis attached
              to this proposal. Please run an an&lt;/ns6:Short>
              &lt;ns6:Long>There is no successfully completed Engineering Analysis attached to
              this proposal. Please run an analysis or provide a detailed
              justification for omitting it.&lt;/ns6:Long>
              &lt;ns6:Reference>Prepare &amp;amp; Submit Workflow Rules&lt;/ns6:Reference>
              &lt;/MessageText>
              &lt;Justification author="USA:AF:CN:4606deaf8ccb473" cls="U">test&lt;/Justification>
              &lt;/ResultEvent>
              &lt;/Results>
                             

              </content>
              <type>ComplianceResult</type>
              <format>ComplianceResultXML</format>
              </attachment>
              </AttachmentList>
              • 4. Re: extracting xmltype and excluding All namespace tags?
                Jason_(A_Non)
                Please look at the FAQ for how to format your XML so that it retain the original formatting.

                I mention that because the XML that appears in your post is not valid as shown due to the existence of a prolog following the <content> node. I am unsure whether this means all data within the <content> node is encoded or whether some CDATA tags are missing. I had thought encoded, but the value of <ns6:Reference> already shows an encoded ampersand so I'm unclear on just what your XML really looks like.

                Regardless, my first answer still stands. You will need to apply a stylesheet to the extracted XML in order to get it into the format you desire. I gave you the start from the SQL side. There are lots of examples of stylesheets that remove namespaces if you search for them.
                • 5. Re: extracting xmltype and excluding All namespace tags?
                  939605
                  Thanks again for you reply and taking the time to solve my issue.
                  I have been looking into the steps that you told me and learning more things but still haven't found my solution yet.

                  Actually I had to cut the content of the xmltype short cuz it was too long of a document and all I wanted was whatever is in element <content> </content> to be displayed without all the junk that there is. Basically I am an Oracle DBA and new to xml and trying to find a solution to the software that I am not familiar with.

                  We have 3 elemetents in this xmltype <content> </content>, <type> </type> and <format> </format> where format and type display ok but because of the namespaces <content> is misbehaving. It puts all the namespaces which make it look uglier.

                  I am starting to read on the following link now.

                  http://www.xmlmaster.org/en/article/d01/c07/#whatis

                  I do appreciate again for you value input and others if they can guide me to some other example type websites where i can learn and solve my issue.

                  Thanks
                  Ayub
                  • 6. Re: extracting xmltype and excluding All namespace tags?
                    odie_63
                    but because of the namespaces <content> is misbehaving. It puts all the namespaces which make it look uglier.
                    Sorry, that's hardly a logical explanation.

                    The content element contains an escaped XML payload, so I guess you need to retrieve it as a "standalone" XMLType in the end?
                    Just like in this recent thread : {thread:id=2390962}

                    My previous question is still half-answered : what output do you require? and additionally what do you want to do with it afterwards?

                    Do you need a resulting XMLType containing this ?
                    <Results xmlns="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/3.100"
                    xmlns:ns6="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Messages/3.100"
                    xmlns:ns5="http://optimal.com/smti/sharedservices/Compliance/Analysis/ValidatorEvents/3.100"
                    xmlns:ns3="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Summaries/3.100">
                    <Header proposalID="USA:AF:AS:fe0a0921268547d" complianceID="92304">
                    ...
                    </Results>
                    If so, you must project the content value as a CLOB instead and convert it to XMLType in a separate step.
                    Once you have the Results document in its own variable/column, you can further process it using XMLTable, in the same way.

                    I'm pretty sure we can show you efficient methods to achieve what you want but we have to know what it is.
                    Throwing away namespaces is probably not the answer to your problem.
                    • 7. Re: extracting xmltype and excluding All namespace tags?
                      939605
                      Sorry, that's hardly a logical explanation.

                      --> my apology for not being precise with my explanation. since I have very little knowledge of xml. I will update my scenario with my steps so even if I mis-stated something demo will explain it better.

                      The content element contains an escaped XML payload, so I guess you need to retrieve it as a "standalone" XMLType in the end?

                      --> very good point. I will put <content> into a variable and try to convert that to standalone xmltype at the end.

                      My previous question is still half-answered : what output do you require? and additionally what do you want to do with it afterwards?

                      --> actually I want to insert element <content> into a separate column as a plain text (without namespace tags n etc)


                      Do you need a resulting XMLType containing this ?
                      <Results xmlns="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/3.100"
                      xmlns:ns6="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Messages/3.100"
                      xmlns:ns5="http://optimal.com/smti/sharedservices/Compliance/Analysis/ValidatorEvents/3.100"
                      xmlns:ns3="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Summaries/3.100">
                      <Header proposalID="USA:AF:AS:fe0a0921268547d" complianceID="92304">
                      ...
                      </Results>

                      --> No I do not need the above tags and xmlns as a Result.

                      If so, you must project the content value as a CLOB instead and convert it to XMLType in a separate step.
                      Once you have the Results document in its own variable/column, you can further process it using XMLTable, in the same way.

                      I'm pretty sure we can show you efficient methods to achieve what you want but we have to know what it is.

                      --> that's what I am hoping for an efficient methods to achieve my goal.
                      Throwing away namespaces is probably not the answer to your problem.

                      --> please let me know so I can update my client about this issue so we are not wasting our efforts on this issue.

                      Thanks again and I will post my demo scenario shortly
                      Ayub
                      • 8. Re: extracting xmltype and excluding All namespace tags?
                        939605
                        create table document_version
                        (doc_id                number(15),
                        document_content      xmltype
                        );

                        create table document_attachments
                        (doc_id               number(15),
                        content               xmltype,
                        type                    varchar2(100),
                        format               varchar2(100)
                        );

                        insert into document_version
                        values (11, ‘<AttachmentList xmlns="http://optimal.com/SP/2/RandPCommon/DocumentStore/GatewayRequestStore/1.0" xmlns:ns0="http://optimal.com/SP/2/RandPCommon/DocumentStore/GatewayRequestStore/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                        <attachment>
                        <content><?xml version="1.0" encoding="UTF-8"?>
                        <Results xmlns="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/3.100"
                        xmlns:ns6="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Messages/3.100"
                        xmlns:ns5="http://optimal.com/smti/sharedservices/Compliance/Analysis/ValidatorEvents/3.100"
                        xmlns:ns3="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Summaries/3.100">
                        <Header proposalID="USA:AF:AS:fe0a0921268547d" complianceID="92304">
                        <Summary compliant="true" jobActSerialID="USA:AF:JA:cfef3d61d93745b"
                        userSerialID="USA:AF:CN:4606deaf8ccb473" status="COMPLETE"
                        requestedBy="FORWARD" updated="2012-04-03T08:02:40.485000Z"
                        created="2012-04-03T08:02:40.220000Z" overallCls="U">
                        <ns3:Events infCount="0" rspCount="0" wrnCount="6" errCount="0">6</ns3:Events>
                        <ns3:Justifications missing="0" rspCount="0" wrnCount="6">6</ns3:Justifications>
                        <MessageText>
                        <ns6:Short>There is no successfully completed Engineering Analysis attached
                        to this proposal. Please run an an</ns6:Short>
                        <ns6:Long>There is no successfully completed Engineering Analysis attached to
                        this proposal. Please run an analysis or provide a detailed
                        justification for omitting it.</ns6:Long>
                        <ns6:Reference>Prepare &amp; Submit Workflow Rules</ns6:Reference>
                        </MessageText>
                        <Justification author="USA:AF:CN:4606deaf8ccb473" cls="U">test</Justification>
                        </ResultEvent>
                        </Results>
                        
</content>
<type>ComplianceResult</type>
<format>ComplianceResultXML</format>
</attachment>
</AttachmentList>’);
                        -- BTW the above xmltype is not a complete, I had to cut it short so it will fit on the page and not cause 10 pages of boring xmltype.
                        commit;
                        insert into document_attachments
                        select 11, xt.attachment_content, xt.attachment_type, xt.attachment_format
                        FROM document_version dva,
                        XMLTABLE (
                        XMLNamespaces('http://oracle.com/test1/1.0'),
                        'AttachmentList/Attachment'

                        PASSING document_content
                        COLUMNS
                        attachment_content xmltype path 'content',
                        attachment_type varchar2(100) path 'type',
                        attachment_format varchar2(100) path 'format') xt;

                        Now if I query document_attachments table retrieving content column it displays the results but with all namespace tags and etc.

                        Thanks
                        • 9. Re: extracting xmltype and excluding All namespace tags?
                          odie_63
                          Ok, thanks for the effort, however that's not a working test case, by far...

                          The XML input is invalid because you've probably just copied the unescaped version and paste it here.
                          The cuts you've done in the XML payload render it invalid too.

                          The query is not the real one either, XMLNamespaces syntax is wrong, as well as the XPath.

                          I understand you want to present a simplified scenario but you're actually wasting time in not giving something we can run.
                          Now if I query document_attachments table retrieving content column it displays the results but with all namespace tags and etc.
                          As said, I doubt the query you're giving will ever retrieve anything, but that's another problem.

                          What I would like to know since my first reply is why namespaces are such a problem for you? They're part of the document, and it's easy to deal with them once we know how to do it.

                          For example, this will retrieve the content value as CLOB (plain text) and convert it to XMLType on the fly :
                          INSERT INTO document_attachments (doc_id, content, type, format)
                          SELECT dva.doc_id
                               , xmlparse(document xt.attachment_content) as content
                               , xt.attachment_type as type
                               , xt.attachment_format as format
                          FROM document_version dva
                             , XMLTable (
                                 XMLNamespaces(default 'http://optimal.com/SP/2/RandPCommon/DocumentStore/GatewayRequestStore/1.0')
                               , 'AttachmentList/attachment'
                                 PASSING document_content
                                 COLUMNS
                                   attachment_content clob          path 'content'
                                 , attachment_type    varchar2(100) path 'type'
                                 , attachment_format  varchar2(100) path 'format'
                               ) xt
                          ;
                          Now, the content column is :
                          <?xml version="1.0" encoding="UTF-8"?>
                          <Results xmlns="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/3.100"
                                   xmlns:ns6="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Messages/3.100"
                                   xmlns:ns5="http://optimal.com/smti/sharedservices/Compliance/Analysis/ValidatorEvents/3.100"
                                   xmlns:ns3="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Summaries/3.100">
                           <Header proposalID="USA:AF:AS:fe0a0921268547d" complianceID="92304">
                            <Summary compliant="true" jobActSerialID="USA:AF:JA:cfef3d61d93745b"
                                     userSerialID="USA:AF:CN:4606deaf8ccb473" status="COMPLETE"
                                     requestedBy="FORWARD" updated="2012-04-03T08:02:40.485000Z"
                                     created="2012-04-03T08:02:40.220000Z" overallCls="U">
                             <ns3:Events infCount="0" rspCount="0" wrnCount="6" errCount="0">6</ns3:Events>
                             <ns3:Justifications missing="0" rspCount="0" wrnCount="6">6</ns3:Justifications>
                            </Summary>
                           </Header>
                           <ResultEvent eventID="K17" serial="USA:AF:LT:4341b33adc0442a" type="COMPLIANCE"
                                        level="WARNING" code="XTX0025" source="NTIA-XSL">
                            <ns5:Node serial="USA:AF:LT:4341b33adc0442a">
                             <ns5:epath>/1/2/28/8</ns5:epath>
                             <ns5:xpath>/SSRF/Body/Location[@serial='USA:AF:LT:4341b33adc0442a']/Address[1]/@country</ns5:xpath>
                             <ns5:values refValueName="country" refValueType="a">
                              <ns5:element name="Address"/>
                              <ns5:attribute name="cityArea">Brownsville</ns5:attribute>
                              <ns5:attribute name="cls">U</ns5:attribute>
                              <ns5:attribute name="country">TX</ns5:attribute>
                             </ns5:values>
                            </ns5:Node>
                            <ns5:Node serial="USA:AF:LT:7c5c3cef2cf74ef">
                             <ns5:epath>/1/2/27/8</ns5:epath>
                             <ns5:xpath>/SSRF/Body/Location[@serial='USA:AF:LT:7c5c3cef2cf74ef']/Address[1]/@country</ns5:xpath>
                             <ns5:values refValueName="country" refValueType="a">
                              <ns5:element name="Address"/>
                              <ns5:attribute name="cityArea">Brownsville</ns5:attribute>
                              <ns5:attribute name="cls">U</ns5:attribute>
                             </ns5:values>
                            </ns5:Node>
                            <MessageText>
                             <ns6:Short>There is no successfully completed Engineering Analysis attached
                                        to this proposal. Please run an an</ns6:Short>
                             <ns6:Long>There is no successfully completed Engineering Analysis attached to
                                       this proposal. Please run an analysis or provide a detailed
                                       justification for omitting it.</ns6:Long>
                             <ns6:Reference>Prepare &amp; Submit Workflow Rules</ns6:Reference>
                            </MessageText>
                            <Justification author="USA:AF:CN:4606deaf8ccb473" cls="U">test</Justification>
                           </ResultEvent>
                          </Results>
                          Is that OK so far?

                          What do you want to do next with the content column?
                          Again there's no need to strip any namespace, or if there really is, please explain what business requirement dictates that and how the attachment is to be processed afterwards.

                          Edited by: odie_63 on 26 mai 2012 19:07
                          • 10. Re: extracting xmltype and excluding All namespace tags?
                            939605
                            What I would like to know since my first reply is why namespaces are such a problem for you? They're part of the document, and it's easy to deal with them once we know how to do it.

                            --> I will talk to my business requirement person tomorrow about it.

                            For example, this will retrieve the content value as CLOB (plain text) and convert it to XMLType on the fly :


                            Is that OK so far?

                            What do you want to do next with the content column?

                            --> My requirement person says she only wants to see the result of content column as starting from
                            <Results>

                            everything in between

                            </Results>
                            Nothing before
                            <?xml version="1.0" encoding="UTF-8"?>
                            <Results xmlns="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/3.100"
                            xmlns:ns6="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Messages/3.100"
                            xmlns:ns5="http://optimal.com/smti/sharedservices/Compliance/Analysis/ValidatorEvents/3.100"
                            xmlns:ns3="http://optimal.com/smti/sharedservices/Compliance/Analysis/Results/Summaries/3.100">

                            Basically we want to eliminate the above line from our content column.
                            <Results>
                            <Results>
                            <Header proposalID="USA:AF:AS:fe0a0921268547d" complianceID="92304">
                            <Summary compliant="true" jobActSerialID="USA:AF:JA:cfef3d61d93745b"
                            userSerialID="USA:AF:CN:4606deaf8ccb473" status="COMPLETE"
                            requestedBy="FORWARD" updated="2012-04-03T08:02:40.485000Z"
                            created="2012-04-03T08:02:40.220000Z" overallCls="U">
                            <ns3:Events infCount="0" rspCount="0" wrnCount="6" errCount="0">6</ns3:Events>
                            <ns3:Justifications missing="0" rspCount="0" wrnCount="6">6</ns3:Justifications>
                            </Summary>
                            </Header>
                            <ResultEvent eventID="K17" serial="USA:AF:LT:4341b33adc0442a" type="COMPLIANCE"
                            level="WARNING" code="XTX0025" source="NTIA-XSL">
                            <ns5:Node serial="USA:AF:LT:4341b33adc0442a">
                            <ns5:epath>/1/2/28/8</ns5:epath>
                            <ns5:xpath>/SSRF/Body/Location[@serial='USA:AF:LT:4341b33adc0442a']/Address[1]/@country</ns5:xpath>
                            <ns5:values refValueName="country" refValueType="a">
                            <ns5:element name="Address"/>
                            <ns5:attribute name="cityArea">Brownsville</ns5:attribute>
                            <ns5:attribute name="cls">U</ns5:attribute>
                            <ns5:attribute name="country">TX</ns5:attribute>
                            </ns5:values>
                            </ns5:Node>
                            <ns5:Node serial="USA:AF:LT:7c5c3cef2cf74ef">
                            <ns5:epath>/1/2/27/8</ns5:epath>
                            <ns5:xpath>/SSRF/Body/Location[@serial='USA:AF:LT:7c5c3cef2cf74ef']/Address[1]/@country</ns5:xpath>
                            <ns5:values refValueName="country" refValueType="a">
                            <ns5:element name="Address"/>
                            <ns5:attribute name="cityArea">Brownsville</ns5:attribute>
                            <ns5:attribute name="cls">U</ns5:attribute>
                            </ns5:values>
                            </ns5:Node>
                            <MessageText>
                            <ns6:Short>There is no successfully completed Engineering Analysis attached
                            to this proposal. Please run an an</ns6:Short>
                            <ns6:Long>There is no successfully completed Engineering Analysis attached to
                            this proposal. Please run an analysis or provide a detailed
                            justification for omitting it.</ns6:Long>
                            <ns6:Reference>Prepare &amp; Submit Workflow Rules</ns6:Reference>
                            </MessageText>
                            <Justification author="USA:AF:CN:4606deaf8ccb473" cls="U">test</Justification>
                            </ResultEvent>
                            </Results>

                            Again there's no need to strip any namespace, or if there really is, please explain what business requirement dictates that and how the attachment is to be processed afterwards.

                            I will talk to her on Tuesday and explained her this important point and will update this thread.

                            I truly appreciate your help on this issue.

                            Thanks
                            • 11. Re: extracting xmltype and excluding All namespace tags?
                              odie_63
                              --> My requirement person says she only wants to see the result of content column as starting from
                              <Results>

                              everything in between

                              </Results>
                              That means namespace prefixes must be removed too, otherwise you'll get an invalid XML.
                              For example :
                              <ns3:Events infCount="0" rspCount="0" wrnCount="6" errCount="0">6</ns3:Events>
                              must be converted to :
                              <Events infCount="0" rspCount="0" wrnCount="6" errCount="0">6</Events>
                              As Jason suggested earlier, it could be done using XSLT (or XQuery), but IMO doing so just for "convenience" is not a good idea.
                              • 12. Re: extracting xmltype and excluding All namespace tags?
                                939605
                                I just wanted to let you all know that I used CLOB conversion and my task was accomplished.

                                select xmltype(x.attachment_content), x.attachment_type, x.attachment_format
                                FROM document_version dv,
                                XMLTABLE(
                                'for $i in /AttachmentList/Attachment
                                where $i/content
                                return $i'
                                PASSING document_content
                                COLUMNS
                                attachment_content CLOB path 'content',
                                attachment_type varchar2(50) path 'type',
                                attachment_format varchar2(50) path 'format') x;

                                Thanks again for you valuable time and suggestions.

                                Edited by: 936602 on Jun 4, 2012 12:14 PM