SQL XMLNAMESPACES

What is SQL XMLNAMESPACES used for?

Declares XML namespace prefixes or a default namespace for use within a single SQL query, usually when generating XML with FOR XML or evaluating XQuery.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL XMLNAMESPACES: SQL Server 2005+, Azure SQL Database, Oracle Database 10g+ (as XMLNAMESPACES clause), IBM Db2 (partial support). Not supported in MySQL, PostgreSQL, or SQLite without extensions.

SQL XMLNAMESPACES Full Explanation

XMLNAMESPACES is a clause (introduced with the WITH keyword in SQL Server and as part of the SELECT list in Oracle) that maps namespace URIs to prefixes or designates a default namespace. The mapping is scoped only to the statement in which the clause appears, letting you:- Qualify element and attribute names when constructing XML with FOR XML PATH, RAW, AUTO, or EXPLICIT.- Reference those prefixes inside value(), query(), exist(), and other XQuery methods.- Avoid hard-coding xmlns declarations in string literals.The clause does not persist any metadata, change global settings, or register schemas. If the same prefix appears twice, the last declaration in the list wins. Declaring a DEFAULT namespace makes any un-prefixed element or attribute belong to that URI.

SQL XMLNAMESPACES Syntax

WITH XMLNAMESPACES (
    'namespace-uri-1' AS prefix1,
    DEFAULT 'default-namespace-uri',
    'namespace-uri-2' AS prefix2
)
SELECT column_list
FOR XML PATH('prefix1:row'), ROOT('prefix1:root');

SQL XMLNAMESPACES Parameters

prefix (string) - The alias used in element or attribute names.
namespace_uri (string) - The namespace URI enclosed in single quotes.
DEFAULT (keyword) - Indicates that the following URI is the default namespace for un-prefixed nodes.

Example Queries Using SQL XMLNAMESPACES

--Example 1: Prefix and default namespace
WITH XMLNAMESPACES (
    'http://schemas.example.com/sales' AS s,
    DEFAULT 'http://schemas.example.com/common'
)
SELECT 1   AS OrderID,
       'John' AS CustomerName
FOR XML PATH('s:order'), ROOT('s:sales');

--Example 2: Use in XQuery
WITH XMLNAMESPACES ('http://schemas.example.com/sales' AS s)
SELECT SalesXml.query('/s:sales/s:order[1]') AS FirstOrder
FROM dbo.SalesDocuments;

Expected Output Using SQL XMLNAMESPACES

Example 1 returns XML with a root element that contains a single element, whose children inherit the default namespace
Example 2 evaluates the XQuery expression using the declared s prefix, returning the matching XML fragment

Use Cases with SQL XMLNAMESPACES

Building XML APIs directly from SQL without hard-coding xmlns attributes.
Creating ISO 20022, UBL, SOAP, or other namespace-heavy XML payloads.
Running XQuery methods against XML columns that store namespaced documents.
Generating test XML where different prefixes map to the same URI for validation.

Common Mistakes with SQL XMLNAMESPACES

Forgetting the DEFAULT keyword when trying to set a default namespace.
Omitting quotes around the namespace URI.
Reusing a prefix already reserved by an XML standard (e.g., xml).
Expecting the declaration to persist outside the statement or to affect other sessions.
Using XMLNAMESPACES without FOR XML or XQuery, which has no effect.

First Introduced In

SQL Server 2005

Frequently Asked Questions

What is the difference between DEFAULT and a named prefix?

DEFAULT assigns a namespace URI to all un-prefixed nodes, while a named prefix requires you to prepend the prefix (e.g., s:order) when referencing the element or attribute.

Does XMLNAMESPACES work without FOR XML?

It only influences FOR XML output and XQuery methods. If neither appears in the statement, the clause has no observable effect.

How can I clear a default namespace?

Declare DEFAULT NULL inside the XMLNAMESPACES list to remove any previously set default for that statement.

Can I nest XMLNAMESPACES clauses?

No. Use a single WITH XMLNAMESPACES clause per statement; list all mappings inside it.