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)
