Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
SQL Query
This example demonstrates the execution of a stored procedure that returns a well-formed XML document from the database. Figure 2 is the logical data model for the SQL Server 2000 Recipes database used in this example.
){kind=link}
Figure 2 ERD of the Recipes database.
The stored procedure uses the new FOR XML EXPLICIT syntax to relate, select, and shape the results of the query into a well-formed XML document (see Listing 2).
Listing 2—SQL Server 2000 stored procedure.
CREATE PROCEDURE [dbo].[usp_GetByKeyword]
@Arg nvarchar(40)
AS
DECLARE @ErrorCode int
SET @ErrorCode = 0
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Recipes!1!element],
'http://www.w3.org/2001/XMLSchema-instance' AS
[Recipes!1!xmlns:xsi],
'Recipes.xsd' AS [Recipes!1!xsi:noNamespaceSchemaLocation],
NULL AS [Recipe!2!ID!hide],
NULL AS [Recipe!2!Title!element],
NULL AS [Recipe!2!Description!element],
NULL AS [Recipe!2!PrepTime!element],
NULL AS [Recipe!2!LeadTime!element],
NULL AS [Recipe!2!Contributor!element],
NULL AS [Recipe!2!ContDate!element],
NULL AS [Recipe!2!Source!element],
NULL AS [Recipe!2!Copyright!element],
NULL AS [Recipe!2!Servings!element],
NULL AS [Ingredient!3!Quantity!element],
NULL AS [Ingredient!3!Units!element],
NULL AS [Ingredient!3!Desc!element],
NULL AS [Instruction!4!Step!element],
NULL AS [Instruction!4!StepOrder!hide],
NULL AS [Keyword!5!Text!element]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
NULL,
NULL,
Recipes.Id AS [Recipe!2!ID!hide],
Recipes.Title AS [Recipe!2!Title!element],
Recipes.Description AS [Recipe!2!Description!element],
Recipes.PrepT AS [Recipe!2!PrepTime!element],
Recipes.LeadT AS [Recipe!2!LeadTime!element],
Recipes.Contributor AS [Recipe!2!Contributor!element],
Recipes.ContDate AS [Recipe!2!ContDate!element],
Recipes.Source AS [Recipe!2!Source!element],
Recipes.Copyright AS [Recipe!2!Copyright!element],
Recipes.Servings AS [Recipe!2!Servings!element],
NULL AS [Ingredient!3!Quantity!element],
NULL AS [Ingredient!3!Units!element],
NULL AS [Ingredient!3!Desc!element],
NULL AS [Instruction!4!Step!element],
NULL AS [Instruction!4!StepOrder!hide],
NULL AS [Keyword!5!Text!element]
FROM
Recipes JOIN Keywords ON Recipes.ID = Keywords.RecipeID
WHERE
Keywords.Keyword LIKE '%' + @Arg + '%'
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
Recipes.Id,
Recipes.Title,
Recipes.Description,
Recipes.PrepT,
Recipes.LeadT,
Recipes.Contributor,
Recipes.ContDate,
Recipes.Source,
Recipes.Copyright,
Recipes.Servings,
Ingredients.Quantity,
Ingredients.Units,
Ingredients.Ingredient,
NULL,
NULL,
NULL
FROM
Recipes JOIN Ingredients ON Recipes.ID = Ingredients.RecipeID
JOIN Keywords ON Recipes.ID = Keywords.RecipeID
WHERE
Keywords.Keyword LIKE '%' + @Arg + '%'
UNION ALL
SELECT DISTINCT
4 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
Ingredients.RecipeID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Instructions.Step,
Instructions.StepOrder,
NULL
FROM
Recipes JOIN Ingredients ON Recipes.ID = Ingredients.RecipeID
JOIN Instructions ON Recipes.ID = Instructions.RecipeID
JOIN Keywords ON Recipes.ID = Keywords.RecipeID
WHERE
Keywords.Keyword LIKE '%' + @Arg + '%'
UNION ALL
SELECT DISTINCT
5 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
Ingredients.RecipeID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Keywords.Keyword
FROM
Recipes JOIN Ingredients ON Recipes.ID = Ingredients.RecipeID
JOIN Instructions ON Recipes.ID = Instructions.RecipeID
JOIN Keywords ON Recipes.ID = Keywords.RecipeID
WHERE
Keywords.Keyword LIKE '%' + @Arg + '%'
ORDER BY
[Recipe!2!ID!hide],
[Instruction!4!StepOrder!hide],
[Keyword!5!Text!element],
Tag
FOR XML EXPLICIT /* , XMLDATA */
IF (@@ERROR <> 0)
SET @ErrorCode = @@ERROR
RETURN @ErrorCode
GO
As you can see in Listing 2, queries that use the FOR XML clause with the EXPLICIT mode have special syntax requirements. The query uses the UNION operator to combine a separate SELECT statement for each level in the resultant XML document hierarchy. As with other SELECT statements, the select list defines the columns that will be in the result set. However, the first and second expressions in the select list must be # as Tag and # as Parent (where # is an integer). The Tag column holds the current element's tag number, whereas the Parent column holds the number of the current element's parent element. These columns are what allow you to specify the parent-child hierarchy in the resulting XML document tree.
Alternatives
Because SQL Server 2000 also offers the easier-to-use FOR XML AUTO syntax that automatically assigns tag names and builds the XML hierarchy, you might wonder why you would go through the extra work associated with manually shaping the results using the EXPLICIT keyword—especially when queries using AUTO can sometimes even outperform those using EXPLICIT if the database is correctly indexed.
However, FOR XML AUTO does not create an XML document that is well-formed. For example, the syntax does not provide a single root element without requiring additional queries. So, if you have to post-process the results obtained from a FOR XML AUTO query (with an XSLT style sheet, for example) to get the desired results, then both ease-of-use and performance favor using EXPLICIT.
In general, using FOR XML EXPLICIT provides the best combination of power and flexibility to provide the capability to shape the data, provide high performance, and support platform-neutral development.