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 I'm about
to go key in)
