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:
- XML Workshop I - Generating XML with FOR XML
- XML Workshop III - FOR XML PATH
- XML Workshop IV - FOR XML EXPLICIT
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.
