Microsoft Dynamics AX 2012 & D365 F&O Blog For Microsoft ERP Business Solution : Create stored procedure to output structured XML using FOR XML EXPLICIT

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