Retrieving Data as XML from SQL Server

[ArticleBot]

This is an article discussion thread for discussing the SitePoint article, "Retrieving Data as XML from SQL Server"

objComm.Properties("Output Stream").Value = objStream

raise an error on my asp script ...
the sored procedure outputs some XML when launched from the query analyser, but it is impossible to retrieve ...

Created the application but get no results. When I try to just view the results of the objStream.ReadText method just using response.write it also show nothing. Testing further, it seems to get hung up on the closing double-quote of the descText. I can see results up to the double quote printing left(objStream.ReadText,n) where n is character before the double quote. Using len(objStream.ReadText), the entire text stream is there but it won;t process pass that double-quote. Any ideas???

TO: TOM
In the case you use W2k server, you should instal .NET Framework. You wont have that problem.

[hgilbert]

Although its nice of MS SQL to have XML extension.
I prefer my custom XML-outputing stored procedure.
You get more control of which values you want as tags or attributes, and you can nest things to your own accord.
At first its hard but then it becomes much easier, simpler and even better than coding inside ASP

[Guimauve]

Of course I have never seen your custom stored proc, but once you have the hang of the SQL Server XML extensions you can format the XML any way that you want. This includes using attributes or elements, nesting elements, etc.

Also it is blazingly fast at formating it for you and there is nothing special that you have to do if you reach that varchar limit of 8000 characters.

[hgilbert]

Cool I am curious
As it is definitely inneficient and counter-productive having me replicating a custom solution if the server can do all that for me much better

And yes the 8000 limit is a very worrying factor.

But I want my XML conforming to my schema.

I am curious whether it is possible/feasible to have MS SQL Server generating the following XML:

(very simplified for demo purpose)

<drink id="1" tag="pink" shortName="Pink Drink">
<nutrition>
<field title="Calories">30</field>
<field title="Sodium">20</field>
<field title="Total Carbohydrate">8</field>
<field title="Sugars">17</field>
<field title="Protein">0</field>
</nutrition>
<contents>
<item>premium pink water</item>
<item>high fructose pink corn</item>
<item>pink juice from concentrate</item>
<item>pink oranges</item>
<item>natural flavours</item>
<item>ascorbic acid</item>
</contents>
</drink>

My transact-SQL is pretty long and somewhat complex
Still I find better faster and more maintainable than using ASP.NET / C#

Basically you have say tables: DRINKS, INGREDIENTS, DRINK_INGREDIENTS

DRINKS: contains all the info including information about Calories, Sugars, Cholesterol, etc ..
If a field is null - that info will be ommited from the final XML.

<field title="cholesterol">0</field> (would be added)
<field title="cholesterol">[NULL]</field> (would be omitted altogether)

INGREDIENTS: contain just common ingredient names for all drinks (and SortOrder)

DRINK_INGREDIENTS: contain which ingredients belong to each drink.
So that gets built into the node <contents>

I could paste the stored procedure - if it helps better explaining what I am trying to achieve.

[hgilbert]

Ok here it goes
please don't laugh!
Also if this can be easily reproduced with transact-SQL XML extensions
I definitely want to study that in-depth.

CREATE PROCEDURE sp_XMLDrinkInfoByID
(
@ID int
)
AS
BEGIN

/*
Purpose: Generates an XML file representative of a
drinks info, contents and nutrition values.
This is to be used with the page selection.aspx

Date Created: 14/11/04
Last Modified: 09/12/04 (added more drink attributes)

*/

/* variable items will hold the contents xml-leaf */

DECLARE @items varchar(8000)

/* this is a iterative hack i read in a forum,
very difficult to reproduce otherwise.
basically - <item>I.Name</item> gets populated adding to
itself until the select query is finished
the end result is a string of <item>I.Name</item> */

SELECT @items = ''
SELECT @items = @items + '<item>' + I.Name + '</item>'
FROM tblIngredients AS I,
tblDrinkIngredients AS DI
WHERE DI.DrinkID = @ID AND I.[ID] = DI.IngredientID
ORDER BY DI.SortOrder

/*

having sorted the drinks contents
we now build the main drink xml format being:

<drink id="@ID" tag="D.Tag" shortName="D.ShortName" longName="D.LongName" ...etc >
<nutrition>
<field title="Calories">D.Calories</field>
<field title="Calories from Fat">D.CaloriesFromFat</field>
<field title="Total Fat">D.TotalFat</field>
<field title="Saturated Fat">D.SaturatedFat</field>
<field title="Cholesterol">D.Cholesterol</field>
<field title="Sodium">D.Sodium</field>
<field title="Total Carbohydrate">D.TotalCarbohydrate</field>
<field title="Dietary Fiber">D.DietaryFiber</field>
<field title="Sugar">D.Sugar</field>
<field title="Protein">D.Protein</field>
<field title="Vitamin A">D.VitaminA</field>
<field title="Iron">D.Iron</field>
<field title="Calcium">D.Calcium</field>
</nutrition>
<ingredients>
@items
</ingredients>
</drink>

Note that <field title="property">D.Column</field> will only be added if
D.Column is not NULL, otherwise we get blank for that line and thus a shorter XML

*/

SELECT '<drink id="' +
CAST(@ID As VarChar(2)) +
'" tag="' + D.Tag +
'" shortName="' + D.ShortName +
'" longName="' + D.LongName +
'" drinkKeywords="' + Cast(D.Keywords AS VarChar(1000)) +
'" drinkTypeKeywords="' + Cast(DT.Keywords AS VarChar(1000)) +
'" drinkTypeID="' + Cast(D.DrinkTypeID As VarChar(2)) +
'"><nutrition>' +
ISNULL('<field title="Calories">'+CAST(D.Calories AS VarChar(4))+'</field>','') +
ISNULL('<field title="Calories from Fat">'+CAST(D.CaloriesFromFat AS VarChar(4))+'</field>','') +
ISNULL('<field title="Saturated Fat">'+CAST(D.SaturatedFat AS VarChar(4))+'</field>','') +
ISNULL('<field title="Cholesterol">'+CAST(D.Cholesterol AS VarChar(4))+'</field>','') +
ISNULL('<field title="Sodium">'+CAST(D.Sodium AS VarChar(4))+'</field>','') +
ISNULL('<field title="Total Carbohydrate">'+CAST(D.TotalCarbohydrate AS VarChar(4))+'</field>','') +
ISNULL('<field title="Dietary Fiber">'+CAST(D.DietaryFiber AS VarChar(4))+'</field>','') +
ISNULL('<field title="Sugars">'+CAST(D.Sugars AS VarChar(4))+'</field>','') +
ISNULL('<field title="Protein">'+CAST(D.Protein AS VarChar(4))+'</field>','') +
ISNULL('<field title="Vitamin A">'+CAST(D.VitaminA AS VarChar(4))+'</field>','') +
ISNULL('<field title="Iron">'+CAST(D.Iron AS VarChar(4))+'</field>','') +
ISNULL('<field title="Calcium">'+CAST(D.Calcium AS VarChar(4))+'</field>','') +
'</nutrition><contents>' + @items + '</contents></drink>'
FROM tblDrinks AS D, tblDrinkTypes AS DT
WHERE D.ID = @ID AND DT.ID = D.DrinkTypeID

END

[Guimauve]

Man!!! This got a little out of hand. Things are made a lot more complicated by the fact that I made the nutrition attributes columns in the drinks table. It would have been a lot easier if there would have been a separate table with the nutrition type and the value for each drink. Anyway, here's the way using XML Explicit in SQL Server.

SELECT 1               AS Tag,
       NULL            AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       NULL            AS [nutrition!2],
       NULL            AS [field!3!title],
       NULL            AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a
UNION
SELECT 2               AS Tag,
       1               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       ''              AS [nutrition!2],
       NULL            AS [field!3!title],
       NULL            AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a
UNION
SELECT 3               AS Tag,
       2               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       ''              AS [nutrition!2],
       'Calories'      AS [field!3!title],
       a.calories      AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a
UNION
SELECT 3               AS Tag,
       2               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       ''              AS [nutrition!2],
       'Sodium'        AS [field!3!title],
       a.sodium        AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a
UNION
SELECT 3               AS Tag,
       2               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       ''              AS [nutrition!2],
       'Carbohydrate'  AS [field!3!title],
       a.Carbohydrate  AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a
UNION
SELECT 3               AS Tag,
       2               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       ''              AS [nutrition!2],
       'Sugars'        AS [field!3!title],
       a.sugars        AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a   
UNION
SELECT 3               AS Tag,
       2               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       ''              AS [nutrition!2],
       'Protein'       AS [field!3!title],
       a.protein       AS [field!3],
       NULL            AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a 
UNION
SELECT 4               AS Tag,
       1               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       NULL            AS [nutrition!2],
       NULL            AS [field!3!title],
       NULL            AS [field!3],
       ''              AS [contents!4],
       NULL            AS [item!5]
  FROM DRINKS AS a 
UNION
SELECT 5               AS Tag,
       4               AS Parent,
       a.id            AS [drink!1!id],
       a.tag           AS [drink!1!tag],
       a.shortName     AS [drink!1!shortName],
       NULL            AS [nutrition!2],
       NULL            AS [field!3!title],
       NULL            AS [field!3],
       ''              AS [contents!4],
       c.name          AS [item!5]
  FROM DRINKS AS a 
 INNER JOIN DRINK_INGREDIENTS AS b ON b.drinkId = a.id
 INNER JOIN INGREDIENTS       AS c ON c.id      = b.IngredientId
 FOR XML EXPLICIT

Charles

[hgilbert]

ah right i can see now
you use unions for nestings
and the [a!b!c] seem to control the attributes (?)
i will study your code in depth
it feels a lot longer but at least we don't run into the dangerous 8000 char limit

XML extensions will take some getting used too.
i will put it up the database and play with it.
hopefully its much faster as well right?

maybe i should have put the sql code for all the tables involved
and I am guilty of not explaining what drinkTypes were for
in fact i was going to keep my example simple like previously - but then changed my mind :P

really thanks for your help - hope I didn't take much of your time.
i know how energy-consuming code writing can be - specially correcting someone elses!

[Guimauve]

Like I said, if the attributes 'Calories', 'Sodium', etc. where rows in another table related to drinks like you did the ingredients, OR if they were attributes of the drink element (like shortName) then the SQL would be a LOT more brief.

Since I didn't have the table definitions I assumed that those columns were part of the drinks table. If that was an incorrect assumption then let me know.

Also you can test the speed difference by doing the following after you have created this as a stored procedure:

DECLARE @Start DATETIME
DECLARE @End DATETIME

SET @Start = getDate()
EXEC sp_XMLGetDrinkInfoByID 1
SET @End = getDate()

PRINT DateDiff(ms,@Start, @End)

SET @Start = getDate()
EXEC sp_XMLGetDrinkInfoByID_New 1
SET @End = getDate()

PRINT DateDiff(ms,@Start, @End)

This will give you the milliseconds it took to run each stored proc.
Charles

[hgilbert]

Yeah I agree my XML is not representative of my tables.

Thanks for the timing code.
It returns 0 milliseconds - too fast?
I have no good book on SQL Server (only that Guru one which its too advanced for me)
So playing with the syntax takes me ages of trial and error and google (hate when I get sybase references!)

These are straight from the database:

tblDrinks
[code]
CREATE TABLE [dbo].[tblDrinks] (
[ID] [int] NOT NULL ,

 [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Tag] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ShortName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [LongName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [DrinkTypeID] [int] NOT NULL ,
 [Calories] [float] NULL ,
 [CaloriesFromFat] [float] NULL ,
 [TotalFat] [float] NULL ,
 [SaturatedFat] [float] NULL ,
 [Sodium] [float] NULL ,
 [TotalCarbohydrate] [float] NULL ,
 [DietaryFiber] [float] NULL ,
 [Sugars] [float] NULL ,
 [Protein] [float] NULL ,
 [VitaminA] [float] NULL ,
 [Iron] [float] NULL ,
 [Calcium] [float] NULL ,
 [Cholesterol] [float] NULL ,
 [Keywords] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_tblDrinks] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

tblDrinkTypes

CREATE TABLE [dbo].[tblDrinkTypes] (
 [ID] [int] NOT NULL ,
 [Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Volume] [int] NOT NULL ,
 [Keywords] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_tblDrinkTypes] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

tblIngredients

CREATE TABLE [dbo].[tblIngredients] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 CONSTRAINT [PK_tblIngredients] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
) ON [PRIMARY]
GO

tblDrinkIngredients

CREATE TABLE [dbo].[tblDrinkIngredients] (
 [DrinkID] [int] NOT NULL ,
 [IngredientID] [int] NOT NULL ,
 [SortOrder] [int] NOT NULL CONSTRAINT [DF_tblDrinkIngredients_Order] DEFAULT (1),
 CONSTRAINT [PK_tblDrinkIngredients] PRIMARY KEY  CLUSTERED 
 (
  [DrinkID],
  [IngredientID]
 )  ON [PRIMARY] 
) ON [PRIMARY]
GO

I am beggining to like stored procedure even more.
Its more intuitive - no library reference ..
fancy doing that in C#
its easier no doubt but uglier (and slower too i guess)

[hgilbert]

i get some output like: 0x442690640443740610670449..
for the earlier XML extension code you provided
also i noticed the @ID is missing.

[hgilbert]

OK maybe doing it all at once its cumbersome.
So I have 3 essential questions - that will help us make the most of XML extensions IF the methods are supported.

1. How can I force a an output to be either a property or a value at will?

<data id="1" name="Sarah" color="violet" />

can be switched to:

<data id="1" color="violet">
   <name>Sarah</name>
</data>

or even to:

<data id="1" name="Sarah">
   <color>violet</color>
</data>

2. How can I add a node inside another node?

XML01 =

<data id="1">
  <name>Sarah</name>
  <color>violet</color>
</data>

XML02 =

<hobbies>
    <item>scuba-diving</item>
    <item>painting</item>
</hobbies>

insert XML02 into XML01 like so:

<data id="1">
   <name>Sarah</name>
   <color>violet</color>
   <hobbies>
      <item>scuba-diving</item>
      <item>painting</item>
   </hobbies>
</data>

Solutions to 1 and 2 is probably what I need to make my previous custom stored-procedure work via pure XML-extensions.

I wonder if it is possible.

[r937]

too bad the article lost my interest on the first page where it says i have to use asp

please let me know when there's a coldfusion version of the article

[hgilbert]

Why should that matter?
I can perfectly read a PHP article and transpose that to ASP.
I have no C# book but a VB.NET for reference despite only programming in C#.

So instead of criticizing you should offer an addendum:
ie: "Now how to do that in ColdFusion .."

bad post for a so-called "mentor".

[hgilbert]

ONLY NOW I get IT !!!

Man, why did you not answer my questions ??????

Originally Posted by hgilbert

1. How can I force a an output to be either a property or a value at will?

<data id="1" name="Sarah" color="violet" />

can be switched to:

<data id="1" color="violet">
   <name>Sarah</name>
</data>

or even to:

<data id="1" name="Sarah">
   <color>violet</color>
</data>

2. How can I add a node inside another node?

XML01 =

<data id="1">
  <name>Sarah</name>
  <color>violet</color>
</data>

XML02 =

<hobbies>
    <item>scuba-diving</item>
    <item>painting</item>
</hobbies>

insert XML02 into XML01 like so:

<data id="1">
   <name>Sarah</name>
   <color>violet</color>
   <hobbies>
      <item>scuba-diving</item>
      <item>painting</item>
   </hobbies>
</data>

The article should be re-written I feel.

Also it was very hard to find a decent article on the subject on the internet.
Most just mumbled away stuff without trying to show things from the very first basic principles.

Congratulations to the writer of this article here
that made things more obvious.

I will tackle XML01 and XML02 to make things more obvious - but today is sunday and supermarket closes early :)

[hgilbert]

SELECT 
	1 AS Tag,
	0 AS Parent,
	1 AS [data!1!id],
	'Sarah' AS [data!1!name],
	'violet' AS [data!1!color]
FOR XML EXPLICIT

Gives

<data id="1" name="Sarah" color="violet" />

[hgilbert]

But

SELECT 
	1 AS Tag,
	0 AS Parent,
	1 AS [data!1!id],
	'Sarah' AS [data!1!name],
	'violet' AS [data!1!color!element]
FOR XML EXPLICIT

gives

<data id="1" color="violet">
<name>Sarah</name>
</data>

[hgilbert]

Now suppose I want to merge:

<hobbies>
    <item>scuba-diving</item>
    <item>painting</item>
</hobbies>

and

<data id="1">
  <name>Sarah</name>
  <color>violet</color>
</data>

to produce:

<data id="1">
   <name>Sarah</name>
   <color>violet</color>
   <hobbies>
      <item>scuba-diving</item>
      <item>painting</item>
   </hobbies>
</data>

what do I do?
I won't take the fun away from anyone
if anyone know the answer please post it here
otherwise I will do it after my shopping

(no i aint bluffing! lol)

[hgilbert]

noone cares

[r937]

perhaps no one knows

but we do care

[hgilbert]

also the output gets cut

i think there is a max of 8000 chars?

[hgilbert]

Solution

SELECT 
   1 AS Tag,
   0 AS Parent,
   1 AS [data!1!id],
   'Sarah' AS [data!1!name!element],
   'violet' AS [data!1!color!element],
   Null AS [hobbies!2!item!element],
   Null AS [hobbies!2!item!element]

UNION ALL

SELECT
   2 AS Tag,
   1 AS Parent,
   Null AS [data!1!id],
   Null AS [data!1!name!element],
   Null AS [data!1!color!element],
   'scuba-diving' AS [hobbies!2!item!element],
   'painting' AS [hobbies!2!item!element]
FOR XML EXPLICIT

outputs:

<data id="1"><name>Sarah</name><color>violet</color><hobbies><item>scuba-diving</item><item>painting</item></hobbies></data>

in Query Analyzer

[hgilbert]

why the 8000 char cap?
after all that we can't use it - because the xml will get cut.

Originally Posted by Guimauve

Of course I have never seen your custom stored proc, but once you have the hang of the SQL Server XML extensions you can format the XML any way that you want. This includes using attributes or elements, nesting elements, etc.

Also it is blazingly fast at formating it for you and there is nothing special that you have to do if you reach that varchar limit of 8000 characters.

yes you do
it gets cut after 8000 char!