This discussion is archived
7 Replies Latest reply: Dec 12, 2012 3:21 PM by mattph RSS

Running XQuery against Audit XML Fails with XMLNS Attributes Specified

mattph Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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