Archive

Posts Tagged ‘Xml’

Create stored procedure to output structured XML using FOR XML EXPLICIT


This article gives the steps to create a stored procedure to output structured XML using For XML Explicit. I am assuming you already know what xml explicit is and you want to know the steps to create it. The article uses SQL Server 2008 R2 and AdventureWorks2008R2 database which can be downloaded from: http://msftdbprodsamples.codeplex.com/

To output data in a customized XML structure, we need to use FOR XML EXPLICIT clause. The recommendation is to use the following steps:

  • Write a select statement per table

use AdventureWorks2008R2;
go
SELECT [BusinessEntityID]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [Person].[Person]

SELECT [BusinessEntityID]
,[AddressID]
,[AddressTypeID]
FROM [Person].[BusinessEntityAddress]

SELECT [AddressTypeID]
,[Name]
FROM [Person].[AddressType]

SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
FROM [Person].[Address]

SELECT [StateProvinceID]
,[Name]
FROM [Person].[StateProvince]

SELECT [BusinessEntityID]
,[PhoneNumber]
,[PhoneNumberTypeID]
FROM [Person].[PersonPhone]

SELECT [PhoneNumberTypeID]
,[Name]
FROM [Person].[PhoneNumberType]
GO

  • Now try to join the statements to group the columns that need to be displayed in one element in your final XML document. For example, we want to display the columns of BusinessEntityAddress, AddressType, and Address in one element.

use AdventureWorks2008R2;
go

SELECT [BusinessEntityID]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [Person].[Person]
SELECT E.[BusinessEntityID]
,A.[AddressID]
,T.[Name]
,[AddressLine1]
,[AddressLine2]
,[City]
,S.[Name]
,[PostalCode]
FROM [Person].[Address] A
INNER JOIN [Person].[BusinessEntityAddress] E
ON A.AddressID = E.AddressID
INNER JOIN [Person].[AddressType] T
ON E.AddressTypeID = T.AddressTypeID
INNER JOIN [Person].[StateProvince] S
ON S.StateProvinceID = A.StateProvinceID

SELECT [BusinessEntityID]
,[PhoneNumber]
,T.[Name]
FROM [Person].[PersonPhone] P
INNER JOIN [Person].[PhoneNumberType] T
ON P.PhoneNumberTypeID = T.PhoneNumberTypeID

GO

  • Now we are ready to output the above statements as one XML file. We will convert the above three result set s to three XML elements with the following hierarchy
<Person personId="1" fname="Ken" mname="J" lname="Sánchez">
<Address AddressId="249" type="Home" AddressLine1="4350 Minute Dr." city="Newport Hills" province="Washington" postalCode="98006" />
<Phone type="Cell" pnumber="697-555-0142" />
</Person>
  • To use FOR XML EXCIPLICT, each result set must have a unit Tag number as a column. For instance, the first result set Tag = 1, the second result set Tag = 2 and the third = 3. The tag number doesn’t appear in the final XML document but it is used as an ID for each result set so that we can relate them to each other. Each result set must have a Parent field as well. The parent field reference the Tag number of the result set’s parent. For example, the second result set’s parent field must be set to 1 which is the tag number of the first result set. The third result set should have the same values for the Tag and Parent fields as the second result set because they are both on the same level and have the same super node

Figure 3 FOR XML EXPLICIT syntax

From the above figure notice the following:

  • We are using Union All clause to Union the three result sets
  • Each result set must have the same number of fields in the select statement
  • First result set’s Parent is set to 0 (can be set to NULL as well) to indicate that it is the Root node while the send and third result sets’ parent is set to 1 to indicate that they are child nodes of Person.
  • Aliases are used for each field.
  • Alias format is [ElementName!TagNumber!AttribteName] and the valid of that attribute is the value of the field. For example, Element Name = Person, TagNumber =1, AttributeName=personId and the value of personId = 1.
  • Fields from other result sets are set to NULL
  • Tag number is not in the output XML file
  • Result sets two and three have a reference to their parent (i.e. person) using the BusinessEntityID (i.e Primary Key)
  • We must use Order By in the last result set otherwise the XML will not be formatted as desired
  • FOR XML EXPLICIT is only referenced in the last result set

use AdventureWorks2008R2;
go

SELECT 1 as Tag,
0 as Parent,
[BusinessEntityID] as [Person!1!personId],
[Title] as [Person!1!title],
[FirstName] as [Person!1!fname],
[MiddleName] as [Person!1!mname],
[LastName] as [Person!1!lname],
NULL as [Address!2!AddressId],
NULL  as [Address!2!type],
NULL  as [Address!2!AddressLine1],
NULL as [Address!2!AddressLine2],
NULL as [Address!2!city],
NULL as [Address!2!province],
NULL as [Address!2!postalCode],
NULL as [Phone!3!type],
NULL as [Phone!3!pnumber]
FROM [Person].[Person]

UNION ALL

SELECT 2 as Tag,
1 as Parent,
E.[BusinessEntityID],
NULL,
NULL,
NULL,
NULL,
A.[AddressID],
T.[Name],
[AddressLine1],
[AddressLine2],
[City],
S.[Name],
[PostalCode],
NULL,
NULL
FROM [Person].[Address] A
INNER JOIN [Person].[BusinessEntityAddress] E
ON A.AddressID = E.AddressID
INNER JOIN [Person].[AddressType] T
ON E.AddressTypeID = T.AddressTypeID
INNER JOIN [Person].[StateProvince] S
ON S.StateProvinceID = A.StateProvinceID

UNION ALL

SELECT 3 as Tag,
1 as Parent,
[BusinessEntityID],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
T.[Name],
[PhoneNumber]
FROM [Person].[PersonPhone] P
INNER JOIN [Person].[PhoneNumberType] T
ON P.PhoneNumberTypeID = T.PhoneNumberTypeID
ORDER BY [Person!1!personId], [Address!2!AddressId]
FOR XML EXPLICIT

  • Run the statement by hitting F5.
  • Click on the output field

Figure 4 Output of statement with FOR XML EXPLICIT clause

  • Validate the structure of the XML Document
  • Save the XML file in a directory with a friendly name

Figure 5 Saving XML document

  • Create a new stored procedure

Figure 6 Creating new stored procedure

  • Copy the select statement and paste it into the body of the stored procedure
  • Execute the script.
  • Validate the stored procedure exists under the stored procedures folder
Categories: Sql Server, Xml Tags: ,