7 Replies Latest reply: Dec 12, 2012 5:21 PM by mattph RSS

    Running XQuery against Audit XML Fails with XMLNS Attributes Specified

    mattph
      I'm working on a school project, part of which requires the demonstration of some XQuery queries. For my project, I've setup an 11.2.0.2 database and set the audit_trail parameter to XML, EXTENDED and have written a few XQuery audit reports. I'm running the XQueries using Oxygen XML 14.0. My queries work fine except for the fact that they always return the following 'empty sequence' error due to the the xmlns attributes in the Audit element:

      Sample Audit Element:
      <Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">

      Error Message:
      Engine name: Saxon-HE XQuery 9.4.0.3
      Severity: warning
      Description: Your query returned an empty sequence.

      If I strip out the xmlns and xsi attributes such that the opening element is just <Audit>, the queries run fine. Does anybody have suggestions as to how to get my Xquery statements to execute properly without having to remove the xmlns attributes the XML?

      Any suggestions would be appreciated. Below is a simple XQuery that just returns the database user and hostname for all audit XML files.

      Thanks,
      Matt

      XQuery Example:

      xquery version "1.0";
      for $a in collection
      ('file:///C:/Project/AuditXML?select=*.xml;recurse=yes')//Audit/AuditRecord

      let $users := $a/DB_User
      where (some $user in $users satisfies ($a/DB_User='DBSNMP'))
      return
      <result>
      {$a/DB_User}
      {$a/Userhost}
      </result>

      Output:

      <?xml version="1.0" encoding="UTF-8"?>
      <result>
      <DB_User>DBSNMP</DB_User>
      <Userhost>localhost.localdomain</Userhost>
      </result>
      <result>
      <DB_User>DBSNMP</DB_User>
      <Userhost>localhost.localdomain</Userhost>
      </result>
        • 1. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
          AlexAnd
          add namespace declaration in your xquery like
          'xquery version "1.0"; (: :)
          declare default element namespace "http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd"; (: :)
          for $a in collection
          ....
          also see http://www.liberidu.com/blog/2008/02/20/howto-namespace-use-with-xmlquery/
          • 2. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
            mattph
            Thank you, that got me past the empty sequence message.

            Due to defining the default namespace though, the results now include a reference to the namespace in the result element tags. This will be fine for my my project, but I was just wondering if there's a way to remove the namespace reference?

            Thanks again,
            Matt

            <?xml version="1.0" encoding="UTF-8"?>
            <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
            <DB_User xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">DBSNMP</DB_User>
            <Userhost xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">localhost.localdomain</Userhost>
            </result>
            <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
            <DB_User xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">DBSNMP</DB_User>
            <Userhost xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">localhost.localdomain</Userhost>
            </result>
            • 3. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
              AlexAnd
              plz see http://beatechnologies.wordpress.com/2008/09/25/stripping-namespace-from-an-xml-using-xquery/
              • 4. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
                mattph
                Thanks, I ended up using the function provided here: http://www.xqueryfunctions.com/xq/functx_remove-attributes.html

                I got my results to look like the below, which will suffice for my purposes. Thanks again for the responses.

                Matt

                <?xml version="1.0" encoding="UTF-8"?>
                <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
                <DB_User>DBSNMP</DB_User>
                <Userhost>localhost.localdomain</Userhost>
                </result>
                <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
                <DB_User>DBSNMP</DB_User>
                <Userhost>localhost.localdomain</Userhost>
                </result>
                • 5. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
                  AlexAnd
                  first of all your xml isn't well formatted
                  root element must be only one like
                  <?xml version="1.0" encoding="UTF-8"?>
                  <root>
                  <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
                  <DB_User>DBSNMP</DB_User>
                  <Userhost>localhost.localdomain</Userhost>
                  </result>
                  <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
                  <DB_User>DBSNMP</DB_User>
                  <Userhost>localhost.localdomain</Userhost>
                  </result>
                  </root>
                  or may be in your case
                  <?xml version="1.0" encoding="UTF-8"?>
                  <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
                  <DB_User>DBSNMP</DB_User>
                  <Userhost>localhost.localdomain</Userhost>
                  </result>
                  for remove you can use xslt as example for xmltransform
                  SQL> select * from v$version where rownum=1;
                   
                  BANNER
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                   
                  SQL> 
                  SQL> with t as
                    2  (select xmltype(
                    3  '<?xml version="1.0" encoding="UTF-8"?>
                    4  <result xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
                    5  <DB_User xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">DBSNMP</DB_User>
                    6  <Userhost xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">localhost.localdomain</Userhost>
                    7  </result>') xml from dual)
                    8  --
                    9  select xmltransform(t.xml, xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                   10  <xsl:output method="xml" indent="no"/>
                   11  <xsl:template match="*">
                   12      <xsl:element name="{local-name()}">
                   13        <xsl:apply-templates select="node()"/>
                   14      </xsl:element>
                   15  </xsl:template>
                   16  </xsl:stylesheet>'))
                   17  from t
                   18  /
                   
                  XMLTRANSFORM(T.XML,XMLTYPE('<X
                  --------------------------------------------------------------------------------
                  <?xml version="1.0" encoding="utf-8"?>
                  <result><DB_User>DBSNMP</DB_User><Userhost>localhost.localdomain</Userhost></res
                   
                  SQL> 
                  --add
                  or try add local-name() to your main xquery

                  Edited by: AlexAnd on Dec 12, 2012 12:05 AM
                  • 6. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
                    odie_63
                    mattph wrote:
                    I ended up using the function provided here: http://www.xqueryfunctions.com/xq/functx_remove-attributes.html
                    It would be better to not generate namespace-qualified elements in the first place instead of trying to remove them afterwards.
                    Declare a prefix for the namespace instead of a default one, then you can directly generate elements in no namespace :
                    xquery version "1.0";
                    declare namespace ns0 = "http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd";
                    for $i in fn:doc("c:/oraclexe/test/audit.xml")/ns0:Audit/ns0:AuditRecord
                    where $i/ns0:DB_User = "SYSMAN"
                    return
                      <result> 
                      {
                        element User { fn:data($i/ns0:DB_User) }
                      , element Host { fn:data($i/ns0:Userhost) }
                      }
                      </result>
                    Output for my test file :
                    <?xml version="1.0" encoding="UTF-8"?><result><User>SYSMAN</User><Host>dev</Host></result>
                    • 7. Re: Running XQuery against Audit XML Fails with XMLNS Attributes Specified
                      mattph
                      Odie, that was exactly what I was looking for, thanks. I had attempted defining a non-default namespace earlier based on a stackoverflow.com article I read, but encountered some problems. I think my problem was that I was missing the fn:data calls in the results area. I'm relatively new to XQuery and XML in general, so I appreciate everyone's help. My queries are nicely formatted now.

                      Thanks again,
                      Matt