Our query examples so far have assumed that element and attribute names were not part of any namespace. It's important to realize that without modification, the query examples will not return any data from XML documents that have namespaces.
 | Best Practice
The query examples will not return any data is because an element without a namespace is distinct from an element by the same name in a particular namespace. The namespace is a critical part of an element's name. Consider the following query:
select tradeid, t.*
from trades, xmltable('$TRADEDOC/FpML'
columns
tradeDate date path 'trade/tradeHeader/tradeDate',
partyId1 integer path 'party[@id="party1"]/partyId',
partyId2 integer path 'party[@id="party2"]/partyId'
) as t;
Figure 66: Query assuming no namespaces |
This query will return rows for all trade documents except the newly inserted document with TRADEID 120 from figure 65, since its path expressions and predicates don't allow for elements and attributes belonging to a namespace. A simple way to make sure that all trades that match the predicate are returned, regardless of namespaces, is to use wildcards in the namespace prefixes. The wildcard "*" matches any namespace as well as no namespace. This query returns rows for all trades, whether the documents have namespaces or not:
select tradeid, t.*
from trades, xmltable('$TRADEDOC/*:FpML'
columns
tradeDate date path '*:trade/*:tradeHeader/*:tradeDate',
partyId1 integer path '*:party[@id="party1"]/*:partyId',
partyId2 integer path '*:party[@id="party2"]/*:partyId'
) as t;
Figure 67: Query with wildcard namespace prefixes
| Best Practice
If you know that all elements you want to query belong to a particular namespace, you can indicate the namespaces using the XMLNAMESPACES function. For XMLTABLE, the default namespace is applied to both the row-generating and all column-generating expressions.
select tradeid, t.*
from trades, xmltable(XMLNAMESPACES
(DEFAULT 'http:columns
tradeDate date path 'trade/tradeHeader/tradeDate',
partyId1 integer path 'party[@id="party1"]/partyId',
partyId2 integer path 'party[@id="party2"]/partyId'
) as t;
Figure 68: Declaring a default element namespace |
Note that the query in figure 68 returns values only from the one trade document with the matching namespace declaration, since the other documents in our sample data have no namespaces.
| Best Practice
While a default namespace is a common solution when only one namespace is present in your documents, you need a different approach if you want to select elements and attributes from multiple specific namespaces. In that case, using namespace prefixes in your query is the best option. See [11] and [5] for more detailed examples. |
Note that other SQL/XML functions require similar attention when dealing with namespaces. In figure 69 the "fpml" prefix is defined in both the XMLQUERY and XMLEXISTS functions of this query. There no construct that allows you to define a namespace for all SQL/XML functions in a query, or even for an entire session.
select tradeid, xmlquery(
'declare namespace fpml="http:;
$TRADEDOC/fpml:FpML/fpml:trade/fpml:tradeHeader/fpml:partyTradeIdentifier')
from trades
where
xmlexists('declare namespace fpml="http:;
$TRADEDOC/fpml:FpML/fpml:trade/fpml:tradeHeader[
fpml:tradeDate=xs:date("2001-04-29Z")]')%
Figure 69: Namespace declarations in XMLQUERY and XMLEXISTS functions
In summary, be careful when writing queries against documents with one or more namespaces. Any XPath expressions need to include suitable default namespace or namespace prefix definitions otherwise your queries will not return the results you expect.
