Topic
  • 2 replies
  • Latest Post - ‏2011-09-20T15:30:22Z by eduardok
eduardok
eduardok
7 Posts

Pinned topic Return value from attribute containing namespace

‏2011-08-09T20:03:59Z |
Given this XML:
<?xml version="1.0" encoding="UTF-8"?>
<test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="/usr/share/path/whatever.xsd"/>

I need a SELECT .... XMLTABLE(....) to return the /usr/share/path/whatever.xsd in the column nNSL of the resultset. One of my many unsuccessful tries was:

SELECT P.nNSL FROM mytable AS U, XMLTABLE('$content/test/xsi:' PASSING U.XML_CONTENT AS "content" COLUMNS "nNSL" CHAR(60) PATH ':@noNamespaceSchemaLocation/string(.)') AS P

Thank you in advance.
Updated on 2011-09-20T15:30:22Z at 2011-09-20T15:30:22Z by eduardok
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Return value from attribute containing namespace

    ‏2011-08-10T08:59:10Z  
    Hi Eduardo,

    I tried the example shown below and both queries return the value "/usr/share/path/whatever.xsd". The key thing is that the attribute noNamespaceSchemaLocation is in the namespace with the prefix "xsi". Thus, the query either needs to use a namespace wildcard (*) or declare the actual namespace in order to return the attribute.

    
    CREATE TABLE mytable(doc XML);   INSERT INTO mytable VALUES (
    '<?xml version="1.0" encoding="UTF-8"?> <test xmlns:xsi=
    "http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation=
    "/usr/share/path/whatever.xsd"> <test2>bla</test2> </test>
    ');     SELECT P.nNSL FROM mytable, XMLTABLE(
    '$content/test' PASSING doc AS 
    "content" COLUMNS nNSL CHAR(60) PATH 
    '@*:noNamespaceSchemaLocation/string(.)') AS P; SELECT P.nNSL FROM mytable, XMLTABLE( XMLNAMESPACES(
    'http://www.w3.org/2001/XMLSchema-instance' AS 
    "xsi"), 
    '$content/test' PASSING doc AS 
    "content" COLUMNS nNSL CHAR(60) PATH 
    '@xsi:noNamespaceSchemaLocation/string(.)') AS P
    


    There is a section "XMLTABLE with Namespaces" in the following article:
    http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/
    It doesn't cover this exact case but it has same useful examples about XMLTABLE and namespaces in general, which might be helpful.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • eduardok
    eduardok
    7 Posts

    Re: Return value from attribute containing namespace

    ‏2011-09-20T15:30:22Z  
    Hi Eduardo,

    I tried the example shown below and both queries return the value "/usr/share/path/whatever.xsd". The key thing is that the attribute noNamespaceSchemaLocation is in the namespace with the prefix "xsi". Thus, the query either needs to use a namespace wildcard (*) or declare the actual namespace in order to return the attribute.

    <pre class="jive-pre"> CREATE TABLE mytable(doc XML); INSERT INTO mytable VALUES ( '<?xml version="1.0" encoding="UTF-8"?> <test xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "/usr/share/path/whatever.xsd"> <test2>bla</test2> </test> '); SELECT P.nNSL FROM mytable, XMLTABLE( '$content/test' PASSING doc AS "content" COLUMNS nNSL CHAR(60) PATH '@*:noNamespaceSchemaLocation/string(.)') AS P; SELECT P.nNSL FROM mytable, XMLTABLE( XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"), '$content/test' PASSING doc AS "content" COLUMNS nNSL CHAR(60) PATH '@xsi:noNamespaceSchemaLocation/string(.)') AS P </pre>

    There is a section "XMLTABLE with Namespaces" in the following article:
    http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/
    It doesn't cover this exact case but it has same useful examples about XMLTABLE and namespaces in general, which might be helpful.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Sorry, never replied, but as you could guess, it worked perfectly, thanks!