SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

XML Workshop X - Working with namespaces

By Jacob Sebastian,


XML uses the concept of Namespaces to avoid ambiguity of elements. It is possible that an XML document is created with two elements having the same name, but referring to or originated from two different contexts. A common example that most tutorials use is of an XML document which has two elements named "table", one referring to an HTML table (having rows and columns) and the other referring to a physical wooden table (say a study table). In this case, each element needs to be distinctly marked to avoid ambiguity.

The primary consumer of an XML document is a piece of software or program. Most of the times, it will be a software or application that will read the data from an XML document. Often, software is not intelligent enough to resolve ambiguity by using contextual references, as humans do. A human can easily differentiate between table 1 (two rows, 3 columns) and table 2 (4 legs, made of steel). But often, software cant do that. An XML parser cannot resolve ambiguity the way we humans often do. Hence XML specification introduced the concept of namespaces, using which, elements belonging to a specific context are identified distinctly.

Resolving Ambiguity

The first time you used this must be in your first program or your first SQL Query. However, we may not have paid attention to that. When we write a TSQL Query, we use aliases to make sure that the columns are taken from the correct table. If you have a query which involves multiple tables, and one of the columns that you are using exists in more than one table, you MUST qualify the column with table alias.

Let us look at an example:

    1 WITH

    2 -- "Employee" Table

    3 Employees AS (

    4     SELECT 'J001' AS Code, 'Jacob' AS EmployeeName, 1 AS Dept

    5     UNION SELECT 'S001', 'Sebastian', 2

    6 ),

    7 -- "Departments" table

    8 Departments AS (

    9     SELECT 1 AS Code, 'IT' AS DepartmentName

   10     UNION SELECT 2, 'Finance'

   11 )


   13 /*

   14 Select values from the two CTEs that we just created.

   15 */


   17 SELECT     *

   18 FROM Employees e

   19 INNER JOIN Departments d ON d.Code = e.dept

   20 WHERE code IS NOT NULL


   22 /*

   23 OUTPUT:


   25 Msg 209, Level 16, State 1, Line 20

   26 Ambiguous column name 'code'.

   27 */

This is a very common error that all of us must have seen a million times. And everyone of you know how to fix it. Here is the correct code.

    1 WITH

    2 -- "Employee" Table

    3 Employees AS (

    4     SELECT 'J001' AS Code, 'Jacob' AS EmployeeName, 1 AS Dept

    5     UNION SELECT 'S001', 'Sebastian', 2

    6 ),

    7 -- "Departments" table

    8 Departments AS (

    9     SELECT 1 AS Code, 'IT' AS DepartmentName

   10     UNION SELECT 2, 'Finance'

   11 )


   13 /*

   14 Select values from the two CTEs that we just created.

   15 */


   17 SELECT     *

   18 FROM Employees e

   19 INNER JOIN Departments d ON d.Code = e.dept

   20 WHERE e.code IS NOT NULL


   22 /*

   23 OUTPUT:


   25 Code EmployeeName Dept        Code        DepartmentName

   26 ---- ------------ ----------- ----------- --------------

   27 J001 Jacob        1           1           IT

   28 S001 Sebastian    2           2           Finance


   30 (2 row(s) affected)

   31 */

The same happens with other programming languages too. It is quite natural that you will end up writing an application that uses more than one class having the same name. In such cases, we use namespaces to differentiate between the two classes. Here is a VB.NET example.

    1 Namespace internet

    2     Public Class Connection

    3         Public Provider As String

    4         Public Speed As String

    5     End Class

    6 End Namespace


    1 Namespace Database

    2     Public Class Connection

    3         Public Provider As String

    4         Public Protocol As String

    5         Public Authentication As String

    6     End Class

    7 End Namespace


    1 Module Module1


    3     Sub Main()

    4         Dim c1 As New Database.Connection

    5         Dim c2 As New internet.Connection


    7         c1.Authentication = "Windows"

    8         c1.Protocol = "TCP/IP"

    9         c1.Provider = "SQL Server Client Provider"


   11         c2.Provider = "World Wide Internet Providers"

   12         c2.Speed = "512 KBPS"

   13     End Sub


   15 End Module

The above sample shows two classes named Connection. The ambiguity is avoided by using a namespace name. I am sure, none of you need a detailed explanation about it. This is something that all of us do every day and is part of our day-to-day programming life.

XML Namespaces

Let us create an XML representation of the above example. The XML may look like this.

    1 <configuration>

    2   <connection>

    3     <provider>World Wide Internet Providers</provider>

    4     <speed>512 KBPS</speed>

    5   </connection>

    6   <connection>

    7     <provider>SQL Client Provider</provider>

    8     <protocol>TCP/IP</protocol>

    9     <Authentication>Windows</Authentication>

   10   </connection>

   11 </configuration>

An application which reads information from the above XML will not be able to identify the correct element that it is supposed to read. It will either throw a validation error or will try to read information from the wrong element. By using namespaces, we can resolve the ambiguity in this example. Here is the new version of the XML.

    1 <configuration

    2     xmlns:net="urn:www.dotnetquest.com/internetconnection"

    3     xmlns:db="urn:www.dotnetquest.com/databaseconnection">

    4   <net:connection>

    5     <net:provider>World Wide Internet Providers</net:provider>

    6     <net:speed>512 KBPS</net:speed>

    7   </net:connection>

    8   <db:connection>

    9     <db:provider>SQL Client Provider</db:provider>

   10     <db:protocol>TCP/IP</db:protocol>

   11     <db:Authentication>Windows</db:Authentication>

   12   </db:connection>

   13 </configuration>

Generating XML

Let us see how to generate an XML structure like the one given above. First of all, let us create the tables that we need and populate them. [Code]

    1 -- Create the table for Net Connection

    2 CREATE TABLE NetConnection (

    3     Provider VARCHAR(50),

    4     Speed VARCHAR(20)

    5 )


    7 -- Populate the Table

    8 INSERT INTO NetConnection(

    9     Provider,

   10     Speed )

   11 SELECT

   12     'World Wide Internet Providers',

   13     '512 KBPS'


   15 -- Create table for DB Connection

   16 CREATE TABLE DbConnection (

   17     Provider VARCHAR(50),

   18     Protocol VARCHAR(20),

   19     [Authentication] VARCHAR(20)

   20 )


   22 -- Populate the Table

   23 INSERT INTO DbConnection (

   24     Provider,

   25     Protocol,

   26     [Authentication] )

   27 SELECT

   28     'SQL Client Provider',

   29     'TCP/IP',

   30     'Windows'

In the initial few sessions of the XML Workshop, we had seen several examples which explained how to generate XML data from the result of a query. If you do not know how to generate XML data from a TSQL query, you should refer to those examples now. You can find the examples here:

The examples we discussed in the above 3 sessions did not touch namespaces. The following example shows how to generate namespace information along with the XML data. [code]


    2 (

    3     'urn:www.dotnetquest.com/internetconnection' AS net,

    4     'urn:www.dotnetquest.com/databaseconnection' AS db

    5 )

    6 SELECT

    7     net.Provider AS 'net:Connection/net:Provider',

    8     net.Speed AS 'net:Connection/net:Speed',

    9     db.Provider AS 'db:Connection/db:Provider',

   10     db.Protocol AS 'db:Connection/db:Protocol',

   11     db.[Authentication] AS 'db:Connection/db:Authentication'

   12     FROM NetConnection net

   13 CROSS JOIN DbConnection db

   14 FOR XML PATH('Configuration')

The above code generates the following XML. [xml]

    1 <Configuration

    2   xmlns:db="urn:www.dotnetquest.com/databaseconnection"

    3   xmlns:net="urn:www.dotnetquest.com/internetconnection">

    4   <net:Connection>

    5     <net:Provider>World Wide Internet Providers</net:Provider>

    6     <net:Speed>512 KBPS</net:Speed>

    7   </net:Connection>

    8   <db:Connection>

    9     <db:Provider>SQL Client Provider</db:Provider>

   10     <db:Protocol>TCP/IP</db:Protocol>

   11     <db:Authentication>Windows</db:Authentication>

   12   </db:Connection>

   13 </Configuration>


In this session we have seen how to generate XML data which contains namespace information. Namespace information is added to the generated XML by using the WITH NAMESPACES TSQL keyword.



sql1.sql | sql2.sql | sql3.sql | sql4.sql | sql5.sql | xml1.xml | xml2.xml | xml3.xml
Total article views: 5473 | Views in the last 30 days: 6
Related Articles

XML Workshop XI - Default Namespaces

The eleventh installment of the XML Workshop continues looking at namespaces. This time Jacob Sebast...





WMI Provider - Invalid Namespace

I am receiving an error: Unable to connect to the Report Server WMI provider. Invalid Namespace wh...


SQL Authentication

SQL User Authentication failed.

sql server 2005