Imagine you have a list of unrelated items in your .NET application, and you need SQL Server to do something for each one. For example:

  • A customer has a shopping cart containing a list of 10 product IDs. The shopping cart is stored in ASP.NET session memory on the web server. How can you retrieve details about these ten products without knocking together some horrific WHERE clause, or executing 10 separate SELECT statements?
  • An administration section of an application allows a user to mass-edit a list of items, and save them all with one click. But the usp_updateItem stored procedure can only save one item at a time.

To minimise the number of round-trips to the database, you need to pass in multiple items at once to the same stored procedure. This is where an XML type parameter can help.

Here’s a fragment of XML containing the list of employee names and IDs I want to pass to my stored procedure:

<employees>
      <employee employeeId="401312" name="John Smith" />
      <employee employeeId="345334" name="John Doe" />
      <employee employeeId="997889" name="Jane Doe" />
</employees>

I’ll populate a table variable (so I can JOIN on it later) with an XPath query using the XML data type’s nodes() method. The technical term for this is shredding, which is pretty rad.

CREATE PROCEDURE FooBar(@employees XML)
AS
BEGIN
      -- Create a table variable to store my items.
      DECLARE @employee TABLE(EmployeeID INT, Name VARCHAR(20))

      -- Shred data carried in the XML and populate the table variable with it.
      INSERT INTO @employee
      SELECT
            e.value('@employeeId', 'INT'),
            e.value('@name', 'VARCHAR(20)')
      FROM
            @employees.nodes('//employee') Employee(e)

      -- Select from table variable as usual.
      SELECT * FROM @employee e
END

Easy, huh? You can easily pass in a set of values with one XML parameter and a couple of lines of T-SQL. Note that you can of course simply shred the XML directly, as part of a bigger query – the temporary table variable is completely optional.

Passing in multiple columns isn’t a problem either. In fact, if you want to go really crazy with this stuff, you could even handle n-dimensional data structures by using nested XML elements.

This is my last article on T-SQL, by the way. I promise.

September 17th, 2008 | No Comments Yet

During my foray into XML SQL queries this week, I was presented with another challenge. Instead of getting just the immediate children of a category, I now needed to recursively select all children from a tree – to an unlimited depth.

A Common Table Expression (CTE, aka WITH statement) can also be called recursively, but requires UNION ALL to join the recursive and anchor members — and XML columns can’t be unioned.

Instead, we need a user defined function that returns XML TYPE. It’ll give us a rootless collection of products within a category, and call itself again to get sub-categories. Got it? Here’s the function definition, again using the AdventureWorks database:

CREATE FUNCTION GetProductCategoryChildren
(
	@ParentProductCategoryID INT
)
RETURNS XML
AS
BEGIN
	RETURN
	(
	SELECT
		-- Map columns to XML attributes/elements with XPath selector
		category.ProductCategoryID AS '@id',
		category.Name AS '@name',
		(
			-- Use a sub query for child elements.
			SELECT
				ProductID AS '@id',
				Name AS '@name',
				ListPrice AS '@price'
			FROM
				SalesLT.Product
			WHERE
				ProductCategoryID = category.ProductCategoryID
			FOR
				XML PATH('product'),  -- The element name for each row.
				TYPE -- Column is typed so it nests as XML, not text.
		) AS 'products',
		dbo.GetProductCategoryChildren(category.ProductCategoryID)
			AS 'categories' -- Recursive call to get child categories.
	FROM
		SalesLT.ProductCategory category
	WHERE
		category.ParentProductCategoryID = @ParentProductCategoryID
	FOR
		XML PATH('category'),  -- The element name for each row.
		TYPE -- The root element name for this result set.
	)
END

This function works great. But we still want to get details about the group itself (not just its children), and we still need a root node so we can load it into an XmlDocument. Here’s how to wrap the call to this function to get a root node and details about the parent:

-- Get the parent group's name and child products.
SELECT
	category.ProductCategoryID AS '@id',
	category.Name AS '@name',
	(
		SELECT
			ProductID AS '@id',
			Name AS '@name',
			ListPrice AS '@price'
		FROM
			SalesLT.Product
		WHERE
			ProductCategoryID = category.ProductCategoryID
		FOR
			XML PATH('product'), TYPE
	) AS 'products',
	-- start recursing to get child categories.
	dbo.GetProductCategoryChildren(category.ProductCategoryID) AS 'categories'
FROM
	SalesLT.ProductCategory category
WHERE
	category.CategoryID = 2
FOR
	XML PATH('category'), ROOT('categories')

This is what the output looks like. It’ll go for as many levels of depth as your tree does.

<categories>
  <category id="2" name="Components">
    <categories>
      <category id="8" name="Handlebars">
        <products>
          <product id="808" name="LL Mountain Handlebars" price="44.5400" />
          <product id="809" name="ML Mountain Handlebars" price="61.9200" />
          <product id="810" name="HL Mountain Handlebars" price="120.2700" />
          <product id="811" name="LL Road Handlebars" price="44.5400" />
          <product id="812" name="ML Road Handlebars" price="61.9200" />
        </products>
        <categories>
          <category id="9" name="Bottom Brackets">
            <products>
              <product id="994" name="LL Bottom Bracket" price="53.9900" />
              <product id="995" name="ML Bottom Bracket" price="101.2400" />
              <product id="996" name="HL Bottom Bracket" price="121.4900" />
            </products>
            <categories>
              <category id="11" name="Chains">
                <products>
                  <product id="952" name="Chain" price="20.2400" />
                </products>
              </category>
            </categories>
          </category>
          <category id="10" name="Brakes">
            <products>
              <product id="907" name="Rear Brakes" price="106.5000" />
              <product id="948" name="Front Brakes" price="106.5000" />
            </products>
          </category>
        </categories>
      </category>
      <category id="12" name="Cranksets">
        <products>
          <product id="949" name="LL Crankset" price="175.4900" />
          <product id="950" name="ML Crankset" price="256.4900" />
          <product id="951" name="HL Crankset" price="404.9900" />
        </products>
      </category>
    </categories>
  </category>
</categories>

Note I had to rearrange some of the categories in the AdventureWorks database to get deeper nesting.

August 30th, 2008 | No Comments Yet

Today, while doing some work on a highly data (not object) driven .NET application, I needed a query output as XML from the application’s SQL Server 2005 database. I wanted:

  • Nicely formatted and properly mapped XML (e.g. no <row> elements as found in FOR XML RAW mode)
  • To be able to easily map columns to XML elements and attributes
  • A single root node, so I can load it into an XmlDocument without having to create the root node myself
  • Nested child elements
  • Not to have to turn my elegant little query into a huge mess of esoteric T-SQL (as with [Explicit!1!Mode])

I discovered that all this is surprisingly easy to achieve all of these things with SQL Server 2005′s FOR XML PATH mode. (I say surprising, because I’ve tried this sort of thing with FOR XML AUTO a few times before under SQL Server 2000, and gave up each time).

Here’s a quick example I’ve created using the venerable AdventureWorks example database, with comments against all the important bits:

SELECT
	-- Map columns to XML attributes/elements with XPath selectors.
	category.ProductCategoryID AS '@id',
	category.Name AS '@name',
	(
		-- Use a sub query for child elements.
		SELECT
			ProductID AS '@id',
			Name AS '@name',
			ListPrice AS '@price'
		FROM
			SalesLT.Product
		WHERE
			ProductCategoryID = category.ProductCategoryID
		FOR
			XML PATH('product'), -- The element name for each row.
			TYPE -- Column is typed so it nests as XML, not text.

	) AS 'products' -- The root element name for this child collection.
FROM
	SalesLT.ProductCategory category
FOR
	XML PATH('category'), -- The element name for each row.
	ROOT('categories') -- The root element name for this result set.

As you can see, we’ve mapped columns to attributes/elements with XPath selectors, and set row and root element names with PATH() and ROOT() respectively.

Plus, by specifying my own names for everything, I was also able to address the difference in capitalization, prefixing and pluralization style between the AdventureWorks’ database table names and common XML.

Running this query produces output in the following format. Note the root nodes for both outer and child collections:

<categories>
  <category id="4" name="Accessories" />
  <category id="24" name="Gloves">
    <products>
      <product id="858" name="Half-Finger Gloves, S" price="24.4900" />
      <product id="859" name="Half-Finger Gloves, M" price="24.4900" />
      <product id="860" name="Half-Finger Gloves, L" price="24.4900" />
      <product id="861" name="Full-Finger Gloves, S" price="37.9900" />
      <product id="862" name="Full-Finger Gloves, M" price="37.9900" />
      <product id="863" name="Full-Finger Gloves, L" price="37.9900" />
    </products>
  </category>
  <category id="35" name="Helmets">
    <products>
      <product id="707" name="Sport-100 Helmet, Red" price="34.9900" />
      <product id="708" name="Sport-100 Helmet, Black" price="34.9900" />
      <product id="711" name="Sport-100 Helmet, Blue" price="34.9900" />
    </products>
  </category>
</categories>

You can read more about using FOR XML PATH here.

August 26th, 2008 | 9 Comments