Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- DEFAULT NULL xmlnamespace
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT NULL)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "NULL" was found following "mlnamespaces(DEFAULT".  Expected tokens may include:  "<func_arg_list>".  SQLSTATE=42601 SQLCODE=-104

-- DEFAULT xmlnamespace
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT 'http:/x.y/ns1')) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns="http:/x.y/ns1"/>


-- DEFAULT numeric xmlnamespace
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT 42.73)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "42.73" was found following "mlnamespaces(DEFAULT".  Expected tokens may include:  "<space>".  SQLSTATE=42601 SQLCODE=-104

-- DEFAULT empty xmlnamespace
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT '')) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root/>


-- DEFAULT empty xmlnamespace
SELECT xmlelement(NAME "root", xmlnamespaces(NO DEFAULT)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root/>


-- NULL xmlnamespace uri
SELECT xmlelement(NAME "root", xmlnamespaces(NULL AS ns)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "AS" was found following ", xmlnamespaces(NULL".  Expected tokens may include:  "AND".  SQLSTATE=42601 SQLCODE=-104

-- empty xmlelement containing one namespace
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:NS1="http:/x.y/ns1"/>


-- empty xmlelement with DEFAULT and regular xmlnamespaces
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT 'http:/x.y/ns1', 'http:/x.y/ns2' AS ns2)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns="http:/x.y/ns1" xmlns:NS2="http:/x.y/ns2"/>


-- xmlelement containing a namespace and a text node
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1), 'text node') FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:NS1="http:/x.y/ns1">text node</root>


-- empty xmlelement containing a) xmlnamespace and b) xmlattribute
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1), xmlattributes('val' AS att)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:NS1="http:/x.y/ns1" ATT="val"/>


-- empty xmlelement containing a) xmlattribute and b) xmlnamespace
SELECT xmlelement(NAME "root", xmlattributes('val' AS att), xmlnamespaces('http:/x.y/ns1' AS ns1)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "AS" was found following "aces('http:/x.y/ns1'".  Expected tokens may include:  "AND".  SQLSTATE=42601 SQLCODE=-104

-- empty xmlelement containing two xmlnamespaces
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1, 'http:/x.y/ns2' AS ns2)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:NS1="http:/x.y/ns1" xmlns:NS2="http:/x.y/ns2"/>


-- empty xmlelement containing two xmlnamespaces and one xmlattribute
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1, 'http:/x.y/ns2' AS ns2), xmlattributes('val' AS att)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:NS1="http:/x.y/ns1" xmlns:NS2="http:/x.y/ns2" ATT="val"/>


-- xmlelement containing two xmlnamespaces, one xmlattribute,
-- and a text node.
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1, 'http:/x.y/ns2' AS ns2), xmlattributes('val' AS att), 'text node') FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:NS1="http:/x.y/ns1" xmlns:NS2="http:/x.y/ns2" ATT="val">text node</root>


-- empty root xmlelement containing one xmlnamespace, one xmlattribute,
-- and one xmlelement child (ns1:foo).
-- empty xmlelement child "ns1:foo" containing one xmlnamespace and one
-- xmlelement child (ns2:bar).
-- xmlelement child "ns2:bar" containing a text node.
SELECT
  xmlelement(NAME "root",
    xmlnamespaces('http:/x.y/ns1' AS ns1),
    xmlattributes('val' AS att),
    xmlelement(NAME "ns1:foo",
      xmlnamespaces('http:/x.y/ns2' AS ns2),
      xmlelement(NAME "ns2:bar", 'text node'))
  )
FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL20275N  The XML name "ns2:bar" is not valid. Reason code = "2".  SQLSTATE=42634 SQLCODE=-20275

-- empty root xmlelement containing one xmlnamespace, one xmlattribute,
-- and one xmlelement child (ns1:foo).
-- xmlelement child "ns1:foo" containing one xmlnamespace, one xmlelement
-- child (ns2:bar), and a text node.
-- xmlelement child "ns2:bar" containing a text node.
SELECT
  xmlelement(NAME "root",
    xmlnamespaces('http:/x.y/ns1' AS ns1),
    xmlattributes('val' AS att),
    xmlelement(NAME "ns1:foo",
      xmlnamespaces('http:/x.y/ns2' AS ns2),
      xmlelement(NAME "ns2:bar", 'text node'),
      'mixed content')
  )
FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL20275N  The XML name "ns2:bar" is not valid. Reason code = "2".  SQLSTATE=42634 SQLCODE=-20275

-- empty root xmlelement containing one DEFAULT xmlnamespace, one xmlattribute,
-- and one xmlelement child (foo).
-- xmlelement child "foo" containing one NO DEFAULT xmlnamespace and a text node.
SELECT
  xmlelement(NAME "root",
    xmlnamespaces(DEFAULT 'http:/x.y/ns1'),
    xmlattributes('val' AS att),
    xmlelement(NAME "foo",
      xmlnamespaces(NO DEFAULT),'bar')
  )
FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns="http:/x.y/ns1" ATT="val"><foo xmlns="">bar</foo></root>


-- empty root xmlelement containing one xmlattribute, one xmlnamespace,
-- three child nodes generated by xmlforest and xmltext, one xmlcomment,
-- and a text node generated by xmlconcat.
SELECT
  xmlelement(NAME "root",
    xmlattributes(73 AS att),
    xmlnamespaces('http:/x.y/ns1' AS ns),
    xmlforest(true AS "ns:x", 42 AS "ns:y", xmltext('<&>') AS "ns:z"),
    xmlcomment(':)'),
    xmlconcat('foo', 'bar')
  )
FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "AS" was found following "aces('http:/x.y/ns1'".  Expected tokens may include:  "AND".  SQLSTATE=42601 SQLCODE=-104

-- exceptions --
[IBM][CLI Driver][DB2/LINUXX8664] SQL0198N  The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty.  SQLSTATE=42617 SQLCODE=-198

-- duplicate xmlnamespace entry (ns1)
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS ns1, 'http:/x.y/ns1' AS ns1)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL20276N  The XML namespace prefix "NS1" is not valid. Reason code = "3".  SQLSTATE=42635 SQLCODE=-20276

-- invalid xmlnamespace syntax
SELECT xmlelement(NAME "root", xmlnamespaces('invalid')) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0440N  No authorized routine named "XMLNAMESPACES" of type "FUNCTION" having compatible arguments was found.  SQLSTATE=42884 SQLCODE=-440

-- multiple DEFAULT xmlnamespaces
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT 'http:/x.y/ns1', DEFAULT 'http:/x.y/ns2')) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL20276N  The XML namespace prefix "xmlns" is not valid. Reason code = "3".  SQLSTATE=42635 SQLCODE=-20276

-- multiple NO DEFAULT xmlnamespaces
SELECT xmlelement(NAME "root", xmlnamespaces(NO DEFAULT, NO DEFAULT)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL20276N  The XML namespace prefix "xmlns" is not valid. Reason code = "3".  SQLSTATE=42635 SQLCODE=-20276

-- invalid xmlnamespace prefix (xmlns)
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS xmlns)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:XMLNS="http:/x.y/ns1"/>


-- invalid xmlnamespace prefix (xml)
SELECT xmlelement(NAME "root", xmlnamespaces('http:/x.y/ns1' AS xml)) FROM SYSIBM.SYSDUMMY1;


1


<?xml version="1.0" encoding="UTF-8" ?><root xmlns:XML="http:/x.y/ns1"/>


-- invalid xmlnamespaces URIs
SELECT xmlelement(NAME "root", xmlnamespaces('http://www.w3.org/2000/xmlns/' AS bar)) FROM SYSIBM.SYSDUMMY1;
SELECT xmlelement(NAME "root", xmlnamespaces('http://www.w3.org/XML/1998/namespace' AS bar)) FROM SYSIBM.SYSDUMMY1;
-- invalid DEFAULT xmlnamespace URIs
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT 'http://www.w3.org/2000/xmlns/')) FROM SYSIBM.SYSDUMMY1;
SELECT xmlelement(NAME "root", xmlnamespaces(DEFAULT 'http://www.w3.org/XML/1998/namespace')) FROM SYSIBM.SYSDUMMY1;
-- empty xmlnamespace uri
SELECT xmlelement(NAME "root", xmlnamespaces('' AS ns)) FROM SYSIBM.SYSDUMMY1;
[IBM][CLI Driver][DB2/LINUXX8664] SQL0171N  The statement was not processed because the data type, length or value of the argument for the parameter in position "1" of routine "XMLNAMESPACES" is incorrect. Parameter name: "".  SQLSTATE=42815 SQLCODE=-171