In my last post in covered two options (FOR XML RAW and FOR XML AUTO) to convert relational data into XML representation. Click on this link to read that post (http://sqlenthusiast.blogspot.com/2012/01/xml-and-xquery-language-part-1for-xml.html). In today's post I will explain other two vary important options for the same task.
Here are The Options that I will cover today for converting relational data into xml.
- FOR XML EXPLICIT
- FOR XML PATH
With EXPLICIT and PATH options you can define Format of XML returned manually. With these two options you have total control on the XML returned. The EXPLICIT mode is included for backward compatibility only; it uses proprietary T-SQL syntax for formatting XML. The PATH mode uses standard XML Xpath expressions to define the elements and attributes of the XML you are creating.
In case you have not run the script given in my previous post, to create SAMPLE database and create Doctors and Patients tables in it, then I am give that script here again, so that you can run it to get the database required for demonstration purpose. Here is the script:
Listing 1.1
USE master;
IF DB_ID('SAMPLE') IS NOT NULL
DROP DATABASE SAMPLE;
CREATE DATABASE SAMPLE;
USE SAMPLE;
CREATE TABLE Doctors
(
doctorid int IDENTITY(1,1) NOT NULL,
doctorname VARCHAR(50) NULL
CONSTRAINT PK_Customers PRIMARY KEY(doctorid)
)
GO
CREATE TABLE Patients
(
patientid int IDENTITY(1,1) NOT NULL,
doctorid int NOT NULL,
admissionDate Datetime NULL
CONSTRAINT PK_Patients PRIMARY KEY(patientid)
CONSTRAINT FK_Patients_Customers FOREIGN KEY(doctorid) REFERENCES dbo.Doctors(doctorid)
)
GO
Insert Into Doctors(doctorname)
Values('Ashish Jain'),('Gaurav Pandit')
GO
Insert Into Patients(doctorid, admissionDate)
Values(1, '20120102'),(1, '20120108'),(2, '20120104'),(2, '20120110'),(1, '20120115')
GO
Select * from dbo.Doctors
Select * from dbo.Patients
- FOR XML EXPLICITSQL Server needs specific format of the rowset returned, to process the XML returned in EXPLICIT mode. The first two colum must me Tag an Parent integers, which sets the hierarchy. Tag defines the level of nesting while Parent defines the parent level. You create multiple levels with multiple SELECTs using the UNION ALL operator. The first select defines first level of nesting; second defines second level of nesting, and so on. Every SELECT must include all columns, of course; however, the columns that are not applicable for a specific level must have NULLs instead of a value. You define element and attribute names with column aliases of the SELECT clause in the ElementName!TagNumber!AttributeName! Directive format. If the Directive part is omitted, you are creating attribute-centric XML. You can use the element directive to produce element-centric XML. The following query is an example of producing XML with the EXPLICIT option:
SELECT 1 AS Tag,
NULL AS Parent,
Doctor.doctorid AS [Doctor!1!doctorid],
Doctor.doctorname AS [Doctor!1!doctorname],
NULL AS [Patient!2!Patientid],
NULL AS [Patient!2!admissionDate]
FROM dbo.Doctors AS Doctor
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
Doctor.doctorid AS [Doctor!1!doctorid],
NULL AS [Doctor!1!doctorname],
Patient.Patientid AS [Patient!2!Patientid],
Patient.admissionDate AS [Patient!2!admissionDate]
FROM dbo.Doctors AS Doctor
JOIN dbo.Patients AS Patient
ON Doctor.doctorid = Patient.doctorid
Order BY [Doctor!1!doctorid],
[Patient!2!Patientid]
FOR XML EXPLICIT
The result is:
<Doctor doctorid="1" doctorname="Ashish Jain">
<Patient Patientid="1" admissionDate="2012-01-02T00:00:00" />
<Patient Patientid="2" admissionDate="2012-01-08T00:00:00" />
<Patient Patientid="5" admissionDate="2012-01-15T00:00:00" />
</Doctor>
<Doctor doctorid="2" doctorname="Gaurav Pandit">
<Patient Patientid="3" admissionDate="2012-01-04T00:00:00" />
<Patient Patientid="4" admissionDate="2012-01-10T00:00:00" />
</Doctor>
- FOR XML PATH
In PATH mode, column names and aliases serve as XPath expressions. XPath expressions define the path to the element in the XML generated. Path is expressed in a hierarchical way; levels are delimited with the slash (/) character. By default, every column becomes an element; if you want to generate attribute-centric XML, prefix the alias name with the at (@) character. Here is an example of a simple XPATH query:
SELECT Doctor.doctorid AS [@doctorid],
Doctor.doctorname AS [Doctor/doctorname]
FROM dbo.Doctors AS Doctor
ORDER BY Doctor.doctorid
FOR XML PATH;
The query produces the following xml:
<row doctorid="1">
<Doctor>
<doctorname>Ashish Jain</doctorname>
</Doctor>
</row>
<row doctorid="2">
<Doctor>
<doctorname>Gaurav Pandit</doctorname>
</Doctor>
</row>
As you can see, the PATH mode produced an element for each row of the resulting rowset; in other words, for each doctor, the name of this element is row. If you want to have a different name, use the syntax FOR XML PATH(‘name’).
In EXPLICIT option, If you want to create XML with nested elements for child tables, you have to use subqueries in the SELECT part of the query in the PATH mode. Subqueries have to return a scalar value in SELECT clause. However, you know that a parent row can have multiple child rows; a doctor can have multiple patients. You return a scalar value by returning XML from the subquery. Then the result is returned as a single scalar XML value. You format nested XML from the subquery with the FOR XML clause, as you format XML in outer query. Additionally, you have to use the TYPE directive of the FOR XML clause to produce a value of XML data type, and not XML as text, which cannot be consumed by the outer query. The following query shows this technique for creating the same XML that I created in the FOR XML EXPLICIT part of this section. Note the subquery with the FOR XML PATH clause and the TYPE directive.
SELECT Doctor.doctorid AS [@doctorid],
Doctor.doctorname AS [@doctorname],
(SELECT Patient.patientid AS [@patientid],
Patient.admissionDate AS [@admissionDate]
FROM dbo.Patients AS Patient
WHERE Doctor.doctorid = Patient.doctorid
ORDER BY Patient.patientid
FOR XML PATH('Patient'), TYPE)
FROM dbo.Doctors AS Doctor
ORDER BY Doctor.doctorid
FOR XML PATH('Doctor');
The result of this query is, the same as the result of the query that used EXPLICIT mode to format the XML returned:
<Doctor doctorid="1" doctorname="Ashish Jain">
<Patient patientid="1" admissionDate="2012-01-02T00:00:00" />
<Patient patientid="2" admissionDate="2012-01-08T00:00:00" />
<Patient patientid="5" admissionDate="2012-01-15T00:00:00" />
</Doctor>
<Doctor doctorid="2" doctorname="Gaurav Pandit">
<Patient patientid="3" admissionDate="2012-01-04T00:00:00" />
<Patient patientid="4" admissionDate="2012-01-10T00:00:00" />
</Doctor>
Please share your knowledge and thoughts on this topic and write, if you have any questions or suggessions, in the comment section. In my next post I will cover XML Shredding(converting from XML to relational representation).
