All Forums Database
dnsmkl 2 posts Joined 05/11
13 Apr 2015
XML namespaces; Use namespace of the parent

Questions about teradata XML. (Teradata 14.10)
 
Is it possible to
define namespace prefix only once - in the parent element - and then reuse it in child elements?
This is example of valid XML, that I use for illustration.
Namespace prefix "h:" is defined only in <h:table> and then reused in <h:tr> and <h:td>.

<root>

<h:table xmlns:h="http://www.w3.org/TR/html4/">
  <h:tr>
    <h:td>Apples</h:td>
    <h:td>Bananas</h:td>
  </h:tr>
</h:table>

</root> 

(similar to example in http://www.w3schools.com/xml/xml_namespaces.asp)
 
SQL that works - needs repetition of namespaces:
(which is unnecessary verbous, tedious and possibly error prone)

SELECT 
xmlserialize(
    content
        XMLELEMENT(name "root",
            XMLELEMENT(name "h:table", xmlnamespaces('http://www.w3.org/TR/html4/' as "h"),
                XMLELEMENT(name "h:tr", xmlnamespaces('http://www.w3.org/TR/html4/' as "h"),
                    xmlagg(
                        XMLELEMENT(name "h:td", xmlnamespaces('http://www.w3.org/TR/html4/' as "h"),
                            Fruit
                        )
                    )
                )
            )
        )
    as varchar(1000)
    indent size=2
) xml_example

FROM (
    SELECT 1 r,'Bananas' Fruit from (sel 1 f)f
    UNION ALL
    SELECT 1 r,'Apples' Fruit from (sel 1 f)f
) dep
GROUP BY r
;

 
All my attempts to avoid repetition did not work:

-- Try number 1: define namespace in parent and the use in children
-- Raises an error:
-- [HY000] [Teradata][ODBC Teradata Driver][Teradata Database]
-- Error in function XMLELEMENT:  Invalid prefix for element name.
SELECT
xmlserialize(
    content
        XMLELEMENT(name "root",
            XMLELEMENT(name "h:table", xmlnamespaces('http://www.w3.org/TR/html4/' as "h"),
                XMLELEMENT(name "h:tr",
                    xmlagg(
                        XMLELEMENT(name "h:td",
                            Fruit
                        )
                    )
                )
            )
        )
    as varchar(1000)
    indent size=2
) xml_example
FROM (
    SELECT 'Bananas' Fruit from (sel 1 f)f
    UNION ALL
    SELECT 'Apples' Fruit from (sel 1 f)f
) f
CROSS JOIN (
    SELECT 'http://www.w3.org/TR/html4/' html4_uri
) namespace
;
 
 
-- Try number 2: cross join whole html4 namespace decalaration
-- Raises an error:
-- [42000] [Teradata][ODBC Teradata Driver][Teradata Database]
-- Syntax error, expected something like a name or a Unicode delimited identifier between the 'xmlnamespaces' keyword and '('.
SELECT
xmlserialize(
    content
        XMLELEMENT(name "root",
            XMLELEMENT(name "h:table", html4_decl,
                XMLELEMENT(name "h:tr", html4_decl,
                    xmlagg(
                        XMLELEMENT(name "h:td", html4_decl,
                            Fruit
                        )
                    )
                )
            )
        )
    as varchar(1000)
    indent size=2
) xml_example

FROM (
    SELECT 'Bananas' Fruit from (sel 1 f)f
    UNION ALL
    SELECT 'Apples' Fruit from (sel 1 f)f
) f
CROSS JOIN (
    SELECT xmlnamespaces('http://www.w3.org/TR/html4/' as "h") html4_decl
) namespace
;
 
 
-- Try number 3: cross join only uri of html4 namespace -- Raises an error: -- [42000] [Teradata][ODBC Teradata Driver][Teradata Database] -- Syntax error: expected something between the 'xmlnamespaces' keyword and '('. SELECT
xmlserialize(
    content
        XMLELEMENT(name "root",
            XMLELEMENT(name "h:table", xmlnamespaces(html4_uri as "h"),
                XMLELEMENT(name "h:tr", xmlnamespaces(html4_uri as "h"),
                    xmlagg(
                        XMLELEMENT(name "h:td", xmlnamespaces(html4_uri as "h"),
                            Fruit
                        )
                    )
                )
            )
        )
    as varchar(1000)
    indent size=2
) xml_example

FROM (
    SELECT 'Bananas' Fruit from (sel 1 f)f
    UNION ALL
    SELECT 'Apples' Fruit from (sel 1 f)f
) f

CROSS JOIN (
    SELECT 'http://www.w3.org/TR/html4/' html4_uri
) namespace
;
 
If it is not possible now,
maybe included in future plans?

Tags:
You must sign in to leave a comment.