Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop X - Working with namespaces

By Jacob Sebastian,

Introduction

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 )

   12 

   13 /*

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

   15 */

   16 

   17 SELECT     *

   18 FROM Employees e

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

   20 WHERE code IS NOT NULL

   21 

   22 /*

   23 OUTPUT:

   24 

   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 )

   12 

   13 /*

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

   15 */

   16 

   17 SELECT     *

   18 FROM Employees e

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

   20 WHERE e.code IS NOT NULL

   21 

   22 /*

   23 OUTPUT:

   24 

   25 Code EmployeeName Dept        Code        DepartmentName

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

   27 J001 Jacob        1           1           IT

   28 S001 Sebastian    2           2           Finance

   29 

   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

    7 

    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

    8 

    1 Module Module1

    2 

    3     Sub Main()

    4         Dim c1 As New Database.Connection

    5         Dim c2 As New internet.Connection

    6 

    7         c1.Authentication = "Windows"

    8         c1.Protocol = "TCP/IP"

    9         c1.Provider = "SQL Server Client Provider"

   10 

   11         c2.Provider = "World Wide Internet Providers"

   12         c2.Speed = "512 KBPS"

   13     End Sub

   14 

   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 )

    6 

    7 -- Populate the Table

    8 INSERT INTO NetConnection(

    9     Provider,

   10     Speed )

   11 SELECT

   12     'World Wide Internet Providers',

   13     '512 KBPS'

   14 

   15 -- Create table for DB Connection

   16 CREATE TABLE DbConnection (

   17     Provider VARCHAR(50),

   18     Protocol VARCHAR(20),

   19     [Authentication] VARCHAR(20)

   20 )

   21 

   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]

    1 WITH XMLNAMESPACES

    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>

Conclusions

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.

Resources:

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

XML Workshop XI - Default Namespaces

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

FORUM
FORUM

CONNECT SQL vs AUTHENTICATE SERVER ???

difference between CONNECT SQL vs AUTHENTICATE SERVER

FORUM

WMI Provider - Invalid Namespace

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

FORUM

SQL Authentication

SQL User Authentication failed.

Tags
namespaces    
sql server 2005    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones