Hi Everyone ,
Today I want to share some tips on how to create XML files using FOR XML clause in SQL SERVER.
Sql server 2008 Provides different methods to generate XML files by using FOR XML clause .The different methods available are listed below
1) FOR XML RAW
2) FOR XML AUTO
3) FOR XML PATH
Now we will see each of them in detail. Before that we will first see each of them in brief.
RAW [ ('ElementName') ]
It is useful for ad hoc FOR XML querying when the structure of the resultant Xml is not known from beginning. While using this you have to be very careful because the result of this can change dramatically when the underlying data table structure is changed.
It takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag. You can optionally specify a name for the row element. The resulting XML output uses the specified Element Name as the row element generated for each row.
This is also useful for ad hoc FOR XML querying. This is mainly useful when you need to map the XML result back to the original columns in the source table. Its result can also change dramatically when the underlying data table structure is changed.
It Returns query results in a simple, nested XML tree. Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.
PATH [ ('ElementName') ]
This is designed for explicitly defining XML result structure. This is more appropriate option than above two while you are working in production environment because you always know the resultant XML structure.
It generates a <row> element wrapper for each row in the result set. You can optionally specify an element name for the <row> element wrapper. If an empty string is provided, such as FOR XML PATH (”) , a wrapper element is not generated. Using PATH may provide a simpler alternative to queries written using the EXPLICIT directive.
Let us jump to the implementation of them . In the below examples i have used AdventureWorks Database . You can find the AdventureWorks Database from the Microsoft Codeplex at http://msftdbprodsamples.codeplex.com/
First we will see how to work with XML RAW.
– 1)For Xml RAW
SELECT * FROM HUMANRESOURCES.EMPLOYEE FOR XML RAW(‘EMPLOYEES’)
- XML File Generated by FOR XML RAW
If u observe every row in the table is generated as a seperate tag in the XML File and all the column values in the table are represented as attributes to the XML Row tag.The element name specified in the RAW(‘ElementName’) would be the tag name for every row in XML file generated . We can further modify the structure of XML generated .
–i) Adding Root Element
SELECT EmployeeID,ContactID,ManagerID,Title FROM HumanResources.Employee FOR XML RAW (‘EMPLOYEE’) ,ROOT(‘EMPLOYEES_DETAILS’)
- Xml Generated using For XML Clause With Root Directive
By using Root directive we can add root element to the resultant XML.
–ii) Creating Elements Instead Of Attributes
SELECT EmployeeID,ContactID,ManagerID,Title FROM HumanResources.Employee FOR XML RAW (‘EMPLOYEE’) ,ROOT(‘EMPLOYEES_DETAILS’) , ELEMENTS
Elements Directive would generate XML Elements rather than XML Attributes
For Xml with Element Directive
Now we will go to the For XMLAuto
– 2)For Xml Auto
SELECT * FROM HUMANRESOURCES.EMPLOYEE as Emp FOR XML AUTO
One thing we should note here is , For XML Auto we cannot specify Root Directive and this would generate o/p similiar to XML RAW(when no directive is used ).
- XML Generated using For XML Auto
Now we will go further into the XML Path
– 3)For Xml Path
Xml Path is mostly used for xml generation as it would allow us to control the structure of XML file generated.
SELECT * FROM HUMANRESOURCES.EMPLOYEE FOR XML PATH(‘EMPLOYEE_DETAILS’) ,ROOT(‘EMPLOYEES’) ,TYPE
XML File Generated by FOR XML PATH with ROOT Directive
Now let’s understand how our FOR XML query is interpreted by SQL server and how these XML nodes and sub nodes and attributes are created in resultant XML. This is managed by certain rules which are explained in brief.
1) Column names in SELECT statement are case sensitive. In our case Employeeid and EMPLOYEEID will be two different scenarios.
2) Columns with name beginning with (@) sign are created as Attribute of every row element created in your result.
3) Columns which are not starting with (@) sign and also not have forward slash (/) are mapped as sub elements of row elements.
4) Columns which has one or more (/) but don’t have (@) sign are mapped as hierarchical data below row element.
5) Columns with same prefix are grouped together as sub elements of it at the same level of hierarchy.
Let us see an example on the above rules .
–Illustration of above rules
SELECT emp.EmployeeID as “Employee/@EmpID” ,
emp_dept.DepartmentID as “Employee/Emp_Dept”,
FROM HumanResources.EmployeeDepartmentHistory emp_dept inner join HumanResources.Employee emp
ON emp_dept.EmployeeID = emp.EmployeeID
FOR XML PATH (‘EMP_DETAILS’) , ROOT(‘EMPLOYEES’) ,ELEMENTS XSINIL
In the above query if you have noticed that we used ELEMENTS XSINIL. As it looks from name it will create an XML element for those values which have null values in database. If we don’t use this and let’s say in our example EndDate column is blank in the database for certain employees and then if we create xml file without ELEMENTS XSINIL then the result would be
XML File Generated by FOR XML PATH without ELEMENTS XSINIL Directive
If you observe there is no tag named EndDate for some employees in the XML generated . To maintain proper XML Structure we have use ELEMENTS XSINIL in those cases .
The o/p of above query would be :
XML File generated by FOR XML PATH using ELEMENTS XSINIL
Hope You all understood the concepts of FOR XML in sqlserver 2008 . I will come back with much more interesting ones in coming future .