TSQL Shred XML - Is this right or is there a better way (newbie @ shredding XML)

Question

Ok, I'm a C# ASP.NET dev following orders: The orders are to take a given dataset, shred the XML and return columns. I've argued that it's easier to do the shredding on the ASP.NET side where we already have access to things like deserializers, etc, and the entire complex of known types, but no, the boss says "shred it on the server, return a dataset, bind the dataset to the columns of the gridview" so for now, I'm doing what I was told. This is all to head off the folks who will come along and say "bad requirements".

Task at hand:

Here's my code that works and does what I want it to:

DECLARE @table1 AS TABLE (
    ProductID    VARCHAR(10)
  , Name         VARCHAR(20)
  , Color        VARCHAR(20)
  , UserEntered  VARCHAR(20)
  , XmlField     XML
)

INSERT INTO @table1 SELECT '12345','ball','red','john','<sizes><size name="medium"><price>10</price></size><size name="large"><price>20</price></size></sizes>'
INSERT INTO @table1 SELECT '12346','ball','blue','adam','<sizes><size name="medium"><price>12</price></size><size name="large"><price>25</price></size></sizes>'
INSERT INTO @table1 SELECT '12347','ring','red','john','<sizes><size name="medium"><price>5</price></size><size name="large"><price>8</price></size></sizes>'
INSERT INTO @table1 SELECT '12348','ring','blue','adam','<sizes><size name="medium"><price>8</price></size><size name="large"><price>10</price></size></sizes>'
INSERT INTO @table1 SELECT '23456','auto','black','ann','<auto><type>car</type><wheels>4</wheels><doors>4</doors><cylinders>3</cylinders></auto>'
INSERT INTO @table1 SELECT '23457','auto','black','ann','<auto><type>truck</type><wheels>4</wheels><doors>2</doors><cylinders>8</cylinders></auto><auto><type>car</type><wheels>4</wheels><doors>4</doors><cylinders>6</cylinders></auto>'

DECLARE @x XML
SELECT @x = (
    SELECT 
        ProductID
      , Name
      , Color
      , UserEntered
      , XmlField.query('
            for $vehicle in //auto
            return <auto 
                type = "{$vehicle/type}"
                wheels = "{$vehicle/wheels}"
                doors = "{$vehicle/doors}"
                cylinders = "{$vehicle/cylinders}"
            />')
    FROM @table1 table1
    WHERE Name = 'auto'
    FOR XML AUTO
)

SELECT @x

SELECT 
    ProductID    = T.Item.value('../@ProductID', 'varchar(10)')
  , Name         = T.Item.value('../@Name', 'varchar(20)')
  , Color        = T.Item.value('../@Color', 'varchar(20)')
  , UserEntered  = T.Item.value('../@UserEntered', 'varchar(20)')
  , VType        = T.Item.value('@type' , 'varchar(10)')
  , Wheels       = T.Item.value('@wheels', 'varchar(2)')
  , Doors        = T.Item.value('@doors', 'varchar(2)')
  , Cylinders    = T.Item.value('@cylinders', 'varchar(2)')
FROM   @x.nodes('//table1/auto') AS T(Item)

SELECT @x = (
    SELECT 
        ProductID
      , Name
      , Color
      , UserEntered
      , XmlField.query('
            for $object in //sizes/size
            return <size 
                name = "{$object/@name}"
                price = "{$object/price}"
            />')
    FROM @table1 table1
    WHERE Name IN ('ring', 'ball')
    FOR XML AUTO
)

SELECT @x

SELECT 
    ProductID    = T.Item.value('../@ProductID', 'varchar(10)')
  , Name         = T.Item.value('../@Name', 'varchar(20)')
  , Color        = T.Item.value('../@Color', 'varchar(20)')
  , UserEntered  = T.Item.value('../@UserEntered', 'varchar(20)')
  , SubName        = T.Item.value('@name' , 'varchar(10)')
  , Price       = T.Item.value('@price', 'varchar(2)')
FROM   @x.nodes('//table1/size') AS T(Item)

So for now, I'm trying to figure out if there's a better way to write the code than what I'm doing now... (I have a part 2 to go along with this one...)

Answer 1

Whether shredding the XML on the server as opposed to doing it on the client is good or bad depends on a variety of factors, the requirements may be perfectly valid. Shredding XML on the server, given the extensive support SQL server 2005 and after have for XML (XPath/XQuery/XML indexes) is often a very sensible approach.

However, what you have in your post is an example of semantic modeling of data, using XML. I recommend you go over a couple of white papers:

• Best Practices for Semantic Data Modeling for Performance and Scalability
• XML Best Practices for Microsoft SQL Server 2005
• Performance Optimizations for the XML Data Type in SQL Server 2005
• Performance tips of using XML data in SQL Server

I don't know if the @table1 in your example is just an example or the actual data structure you use in production, but some points will jump out immediately after you read those papers:

• use typed XML when possible (add a schema)
• use an appropriate XML index for the processing you need
• try to shred all XML in one single transformation instead of 3 consecutive steps

And finally, if you need to shred every time you query, perhaps you need to analyze the data model (this is where the first paper in my list is useful).