Need help exporting XML generated with Select ... for XML explicit

Your comments are really inappropriate.  I posted the question honestly and your comments appear to be attacking the question not answering it.

If you reread the question you will see that ASP doesn't meet the required solution and your comments don't contribute.

1. The results produced by SQL and SQL Query Analyzer are identical.  SQL Query analyzer simply provides an administrative interface in which to write and develop queries.  I've yet to see an instance where a query returns different results to ASP then it does to QA.

2. XML explicit does not generate a root element per se.  Regardless of this you'll notice that I explicitly mentioned the need to create a header and footer for the document.

3. Your ASP comment is in appropriate.  ASP doesn't meet the security and automation requirements.  My expirements with an ASP stream object did not produce fruit other then a series of error messages.  I consider ASP a dead avenue because it doesn't meet the requirements.

4. Your remark about the stream object and this being out of the question is non-contributory and inappropriate.

5. Xpath is a access method to the data in SQL that allows queries to be submitted to SQL via a special configuration of IIS.  This presents an unacceptable security risk on this particular server.  I'm not comfortable providing any query level access via webpage.

Please keep comments to the desired solution.  DTS and SQL exporting XML to a file on the server.  If you need additional information please feel free to ask.

To help understand what I'm working on I'll post the stored procedure code.

Caveat Emptor:  
-It isn't finished, and the tag heirarchy is incorrect.  
-My goal at this point is to export the output of this function to a file so that I can view it as XML.  
-I prefer to use a longer term export mechanism so that I don't have to revisit the export method later.
-I'm using a stored procedure for rights management and code maintenance purposes.  
-I have changed some names to mask who this feed is for.  Their may be minor inconsistencies because of this.

Statistical trivia:
Tables involved and definitions:
Products - 800 rows, contains product centric data.
Productcolorsizematrix - 48,000 rows contains matrix of products,colors,sizes,sku,and other pertinent data points.
Prodtypes- 21ish product types that contain department level data on types of products.

XML Produced will contain 48,000 elements in a structure to describe the products in the system.

Code:
CREATE PROCEDURe dbo.happyplace_xml_product_feed AS
select
1 as tag,
null as Parent,
isku as [product!1!sku!element],
mfrsku as [product!1!standardproductid!element],
'a_clth_gen' as [product!1!producttaxcode!element],
'New' as [product!1!condition!element],
products.product_description as [product!1!description!element],
products.product_name as [product!1!title!element],
dbo.findmill(productcode) as [product!1!brand!element],
products.product_description as [product!1!itemdescription!element],
products.bullet1 as [product!1!bulletpoint!element],
products.bullet2 as [product!1!bulletpoint!element],
products.bullet3 as [product!1!bulletpoint!element],
products.bullet4 as [product!1!bulletpoint!element],
products.bullet5 as [product!1!bulletpoint!element],
productcode as [product!1!merchantcatalognumber!element],
products.msrp as [product!1!msrp!element],
dbo.findmill(productcode) as [product!1!manufacturer!element],
mfrsku as [product!1!mfrpartnumber!element],
producttypes.bingodept1 as [product!1!usedfor!element],
producttypes.bingondept2 as [product!1!usedfor!element],
producttypes.bingondept3 as [product!1!usedfor!element],
producttypes.bingondept4 as [product!1!usedfor!element],
producttypes.bingondept5 as [product!1!usedfor!element],
producttypes.stylekeyword1 as [product!1!searchterms!element],
producttypes.stylekeyword2 as [product!1!searchterms!element],
producttypes.stylekeyword3 as [product!1!searchterms!element],
producttypes.stylekeyword4 as [product!1!searchterms!element],
producttypes.stylekeyword5 as [product!1!searchterms!element],
producttypes.lifestyle1 as [product!1!platinumkeywords!element],
producttypes.lifestyle2 as [product!1!platinumkeywords!element],
producttypes.lifestyle3 as [product!1!platinumkeywords!element],
producttypes.lifestyle4 as [product!1!platinumkeywords!element],
producttypes.lifestyle5 as [product!1!platinumkeywords!element],
'false' as [product!1!isgiftwrapavailable!element],
'false' as [product!1!isgiftmessageavailable!element],
'clothing' as [product!1!productdata!element],
skuweight as [product!1!weight!element]

from products  left join productcolorsizematrix on productcode=product_number
inner join producttypes on products.prodtype=producttypes.prodtype
for xml explicit
GO

>>Your comments are really inappropriate.<<
I am sorry you took it that way.  You have to realize this is thread will be read by not only you and I, but many others.  So if I see something as blatantly wrong as "XML explicit does not generate a root element per se" I will point it out.

So let me take each of your comments.
1. No they ar not.  As you have witnessed:  The Xml cannot exceed 8192 or it will be truncated.  QA is an interface and as such has its limitations.  Let me point out another one that you may not be familiar with.  QA uses the ODBC provider <> OLEDB provider in subtle ways that can make it a night mare to switch.

2. As I mentioned previously this is wrong.

3. You are the one that mentioned ASP first, I was merely correcting you.
>>My expirements with an ASP stream object did not produce fruit other then a series of error messages. <<
And that is fine.  But to then jump to the conclusion that it is "very complicated" is IMHO a stretch.

4. And here I agree with you.

5.  No it is not. Suggest you read up on XPath (which has nothing inherently to do with SQL, let alone SQL Server) and everything to do with SQLXml. EOS

>>Please keep comments to the desired solution.<<
Than do yourself a favor and step back and think why you are here.  I am more than prepared to help you, but let me tell you that in order to export Xml using DTS is not straight-forward and the only option that I know of is to use an ActiveX script within the DTS package.

>>It isn't finished, and the tag heirarchy is incorrect.  <<
Ok.  I think I understand you now.  You stated:
>>doesn't have a root element.<<
When in fact you meant to say that your particular implementation of For Xml Explicit does not contain a root element.  In order to produce a root element you will probably have to change your query to one containing a Union statement, this will give you the hierarchy you need.

Try posting the desired Xml output and we can address that first.

Thanks for the reply.

Let's get down to solving the issue.

1. Your are correct on the 8192 rowset limitation.  As I'm not able to see the full resultset that is all I can see right now.  

5. The documentation I read on Xpath suggests that it is an access method.  But then again, I've never cared for Microsoft's documentation.  I always thought it was too much marketing and not enough substance.  

Okay...  

I'd rather avoid ActiveX because I'm not familiar with it.  Let me backtrack to my prime goal.

Primary goal is to take data, format it in XML, and output it for the consumption of an important partner.  

I chose to do this in SQL because it appears that FOR XML EXPLICIT takes the dirty work out of creating the XML.  However, I can see XML-like data, I just can't get it out or see it outside of QA.  Like being trapped in a gold-fishbowl.  :)  Smells like data, looks like data, seems like data, but can't get my hands on the data.

ASP seems to have performance issues.  My current flat file exports run in ASP using query objects because at the time that was easier.  I spent quite a bit of time learning cursors and wrote some other objects that worked well as cursors.  The SQL DTS packages always seem to run blazingly faster then the ADO/ASP pages.  ADO seems to be very slow in opening/closing connections and dragging data back.  For this reason I was trying to learn XML and improve my SQL skills.


I can collect the data in SQL.  That's not the hard part.  I also know how I want to map the data.  SQL is excellent at set based activity and seems like the right place to format the data.

I did consider doing the XML mapping in ASP.  However, I was concerned about performance.  I initiall was going to retrieve it as a recordset but I couldn't figure out how to reference the first column.  I tried about 5 different things before deciding that the stream object looked more promising.

I found 2 examples on using the stream object.  For some reason I couldn't get them to run, I kept getting errors that didn't make sense.  If you really think that is the simplest solution I'll go run the code and fetch the error message.

I also thought about gathering the data, formatting it, and then iterating through it in ASP to create an XML output.  This seems to be the least maintainable solution that is the hardest to automate and live with.  

I'll see if I can find the desired output.  That's a few steps beyond where I'm stuck right this second.

Here's my development strategy.  I might have skipped a step.  This is my first XML project.  Although, I have a fair amount of coding and DB work I've never had to deal with XML.  

1. Map required data points.
2. Build Select statement to get them.
3. Modify select statement to include For xML Explicit and map the elements.
4. Run select statement and see output.
5. Validate output in IE browser.
6. Correct as required until it looks right.
7. Upload test data to test environment and see if it takes.

I have an XDR, but I don't think I can publish it because i'm forbidden to share it and it's laced with references to the partner.  To be honest, I don't really understand XDR vs XLS.  An XDR seems to be a schema file... but the XDR I have is missing tags that are in my example file that I was sent.  It will take me a few minutes to get the sample data in here and I'll put it on it's own post for clarity.

I think I can reformat the union statements once I can get a look at the data... but that was a step 5/6 activity.

Here is the sample data I was provided

This has one product and the desired output will contain 38,000 entries (one for each sku).  Oh what fun.  :)

 <?xml version="1.0" encoding="utf-8" ?>
- <happyEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="happy-envelope.xsd">
- <Header>
  <DocumentVersion>1.01</DocumentVersion>
  <MerchantIdentifier>M_ACME_999</MerchantIdentifier>
  </Header>
  <MessageType>Product</MessageType>
  <PurgeAndReplace>true</PurgeAndReplace>
- <Message>
  <MessageID>241902</MessageID>
- <Product>
  <SKU>2419</SKU>
  <ProductTaxCode>A_CLTH_GEN</ProductTaxCode>
- <DescriptionData>
  <Title>Acme Ribbed Silk Mock Turtleneck Regular Small Ivory</Title>
  <Brand>Acme</Brand>
  <IsGiftWrapAvailable>true</IsGiftWrapAvailable>
  <IsGiftMessageAvailable>true</IsGiftMessageAvailable>
  </DescriptionData>
  <DiscoveryData />
- <ProductData>
- <Clothing>
- <VariationData>
  <Size>Regular Small</Size>
  <Color>Ivory</Color>
  </VariationData>
- <ClassificationData>
  <ClothingType>Sweater</ClothingType>
  <Department>womens</Department>
  <StyleKeywords>turtleneck</StyleKeywords>
  <StyleKeywords>ribbed</StyleKeywords>
  <StyleKeywords>pullover</StyleKeywords>
  <StyleKeywords>mock-turtleneck</StyleKeywords>
  <StyleKeywords>long-sleeve</StyleKeywords>
  <StyleKeywords>fitted</StyleKeywords>
  <PlatinumKeywords>N2380411~2380522~2374367</PlatinumKeywords>
  <MaterialAndFabric>silk</MaterialAndFabric>
  <OccasionAndLifestyle>business-casual</OccasionAndLifestyle>
  <OccasionAndLifestyle>business</OccasionAndLifestyle>
  <EventKeywords>winter</EventKeywords>
  <EventKeywords>valentines-day</EventKeywords>
  <EventKeywords>spring</EventKeywords>
  <EventKeywords>mothers-day</EventKeywords>
  <EventKeywords>graduation</EventKeywords>
  <EventKeywords>fathers-day</EventKeywords>
  <EventKeywords>fall</EventKeywords>
  <EventKeywords>christmas</EventKeywords>
  <EventKeywords>birthday</EventKeywords>
  <EventKeywords>anniversary sale</EventKeywords>
  </ClassificationData>
  </Clothing>
  </ProductData>
  </Product>
  </Message>

I also have created a Excel Spreadsheet that maps out where each element belongs in relation to the next.  I'll try to recreate the data here.  This is basically a template for how to program the structure.

It's an Excel spreadsheet with the levels across the top of the columns.  Each column represents the heirarchy level from 1 on the left moving up as you go write.  Each tag name resides at the level it lives on.  Child and Sibling tags reside at higher levels and the same level.

Data:

1                       2                   3                    4                   5
Product
                        Sku
                        TaxCode
                        Description Data
                                            Title
                                            Brand
                                            Giftwrap
                                            Giftmsg
                        Discoverydata
                        ProductData
                                             Clothing
                                                                    VariationData
                                                                                      Size
                                                                                      Color
                                                                     Classification Data
                                                                                       ClothingType
                                                                                       Department
                                                                                       Stylekeywords (up to 5)
                                                                                       Material and Fabric
                                                                                      OcassionandLifestyle( up to 3)
                                                                                      EventKeywords (up to 5)

I created this so I could visualize the data structure without looking at the sample file or the XDR.  I needed a clean way to view it.  I took the sample data and mapped the tags on paper.  I then took the paper data and put in Excel to help organize my thoughts.  The above info represents what I have in excel.  I suspect I need to union the heck out of the data to produce the results.  I was going to hit this one column at a time and build the select statement iteratively.

Alternately I could construct a temp table that has the data structured properly and then use a For XML Auto.  I think.  I seem to remember reading the XML Auto works fine if you don't have to remap your data.  It seems pretty cumbersome to write all these unions, but I understand cursors should be avoided when possible.  Even though this is a perfect cursor application...

I added the closing "happyEnvelope" tag to make the Xml well-formed.  This should be more readable:

<?xml version="1.0" encoding="utf-8" ?>
<happyEnvelope xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="happy-envelope.xsd">
      <Header>  
            <DocumentVersion>1.01</DocumentVersion>  
            <MerchantIdentifier>M_ACME_999</MerchantIdentifier>  
      </Header>  
      <MessageType>Product</MessageType>  
      <PurgeAndReplace>true</PurgeAndReplace>
      <Message>  
            <MessageID>241902</MessageID>
            <Product>  
                  <SKU>2419</SKU>  
                  <ProductTaxCode>A_CLTH_GEN</ProductTaxCode>
                  <DescriptionData>  
                        <Title>Acme Ribbed Silk Mock Turtleneck Regular Small Ivory</Title>  
                        <Brand>Acme</Brand>  
                        <IsGiftWrapAvailable>true</IsGiftWrapAvailable>  
                        <IsGiftMessageAvailable>true</IsGiftMessageAvailable>  
                  </DescriptionData>  
                  <DiscoveryData />
                  <ProductData>
                        <Clothing>
                              <VariationData>  
                                    <Size>Regular Small</Size>  
                                    <Color>Ivory</Color>  
                              </VariationData>
                              <ClassificationData>  
                                    <ClothingType>Sweater</ClothingType>  
                                    <Department>womens</Department>  
                                    <StyleKeywords>turtleneck</StyleKeywords>  
                                    <StyleKeywords>ribbed</StyleKeywords>  
                                    <StyleKeywords>pullover</StyleKeywords>  
                                    <StyleKeywords>mock-turtleneck</StyleKeywords>  
                                    <StyleKeywords>long-sleeve</StyleKeywords>  
                                    <StyleKeywords>fitted</StyleKeywords>  
                                    <PlatinumKeywords>N2380411~2380522~2374367</PlatinumKeywords>  
                                    <MaterialAndFabric>silk</MaterialAndFabric>  
                                    <OccasionAndLifestyle>business-casual</OccasionAndLifestyle>  
                                    <OccasionAndLifestyle>business</OccasionAndLifestyle>  
                                    <EventKeywords>winter</EventKeywords>  
                                    <EventKeywords>valentines-day</EventKeywords>  
                                    <EventKeywords>spring</EventKeywords>  
                                    <EventKeywords>mothers-day</EventKeywords>  
                                    <EventKeywords>graduation</EventKeywords>  
                                    <EventKeywords>fathers-day</EventKeywords>  
                                    <EventKeywords>fall</EventKeywords>  
                                    <EventKeywords>christmas</EventKeywords>  
                                    <EventKeywords>birthday</EventKeywords>  
                                    <EventKeywords>anniversary sale</EventKeywords>  
                              </ClassificationData>  
                        </Clothing>  
                  </ProductData>  
            </Product>  
      </Message>
</happyEnvelope>

I'll add another comment.

I have support on the content of the XML and on the validity.  The receiving system will generate an error report.  And anything I don't understand I can get good support on.

I don't have support on how to generate the XML and get it out of my SQL system, which is why I turned here.  What I learn on this XML document should be applicable to the other 5.

I see that... that shouldn't be an issue.  I may not have copied the whole sample file and inadvertantly left out the closing tag.  

So how do I get this out of SQL?

You said that DTS involves an Active X script.  If I'm using a stored procedure (SP) to fetch the data and generate the XML shouldn't I be able to write a reasonably simple script to push the data to a file?

Asp Stream I can post the code that I have and perhaps it is something simple I overlooked.  Because I currently generate the data in a flat (text) file I took that code and used it as starting point.  I gutted it and rearranged the sql statement.  I couldn't get it to open the stream object succesffully and I don't recall what the error message was.

I also recall seeing a a dochandle object in books online.

Okay, I'm mainly interested in generating the product tags.  I can add the rest of it because it's reasonably straightforward.  I have to use a batch file to move the output to a process specific directory, so renaming the file and copying a front and back to it should be easy enough.  However, if there is a simple way then I'd rather do it in SQL so I can maintain code in one spot.

Here is the ASP code that I was last working with.  I've modified some names to protect the vendor.

Set happyrecords= Server.CreateObject("ADODB.command")
Set oDom = Server.CreateObject("Msxml2.DOMDocument")
happyrecords.ActiveConnection = MM_CatalogPipe_STRING
happyrecords.commandtext = "execute dbo.happy_xml_product_feed"
happyrecords.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
happyrecords.execute, , 1024
happyrecords.save"g:\test.xml",adpersistxml


response.write odom.xml

I'll be very honest.  I don't have a clue how the stream object works.  I was trying to integrate it into some existing code as a replacement for the command object.  Based on the two examples I had, this is how I thought it was supposed to be done.

I believe what we are doing is this:  (in case I misunderstand)

1. Instantiate DB connection and stream object.
2. Set DB connectionpath, SQL text.
3. Call DB object open and store output in stream object.
4. Output stream object.

4. can be changed to a writefile at a later point in time.

>>I also have created a Excel Spreadsheet that maps out where each element belongs in relation to the next.<<
You really need to get a good Xml editor. Xml Spy is the top of the line, but quite expensive, but there are may others and some are even free.  This will allow you to do XSLT transformations and a whole host of other stuff.

>>I suspect I need to union the heck out of the data to produce the results.<<
Yes, to put it mildly <g> Roughly speaking you will have one union statement for each level.  So this will be a challenge to say the least to keep all the Union statements in sync.  I believe the most I have ever had to do was 3 levels and it was not by any means trivial.  So this could turn out to be the mother of all For Xml Explicit queries.

>>I was going to hit this one column at a time and build the select statement iteratively.<<
That is the correct way to approach it.

>>Alternately I could construct a temp table that has the data structured properly and then use a For XML Auto.<<
That may be your best approach. Although For Xml Auto does not contain a root element.

But to get back to your original questions.  

You cannot assign the result from any FOR XML queries to a variable in T-SQL so while there are very cumbersome kludges that I have heard about, but never actually used, there is no way to save a For Xml result to a table, let alone file.  In order to do this you have to have some client (ASP, VB, .NET, DTS, etc.)  Using either ADO or ADO.NET.

The only way that I know how to extract Xml data is using ADO or ADO.NET.  So that implies using the above mentioned clients.  But if you decide to use the DTS route, than again I would have to resort to the ActiveX scripting found in DTS.  I don't know any other way.  So to conclude, you have two decisions to make:
A. How you are going to extract the Xml. (Using temporary tables and then using For Xml or some other route)
B. How you are going to export the data.

What is an XLST transformation?  I hate to ask obvious questions..  But I don't understand what an XLST is by name.  

Okay, so the DTS route is really nasty.

Did you see anything in particular wrong with my ASP code and the stream object?

As for the data itself.  It seems like the temp table is the most programmer friendly.  That allows me to chunk the query and run it in steps.  Essentially it's the same as the union and perhaps a union is more efficient.  But I have to start somewhere and it makes more sense to do this in steps.

Although this looks intimidating, I think 2 of the levels are empty of data.  so that makes it simpler to build.

Oh, I just remembered.  I thought you could BCP to retrieve the results of a query.  I tried this several times but I kept getting bcp error messages after it appeared that the query had run for a while.  bcp would be a very acceptable alternative to a DTS tool.  I think the syntax was BCP.exe -sServername -dDatabase -N (trusted connectiON) -t -r
It's easily found using google and XML BCP export

I had no idea there were XML editors.... thanks for the recommendation.
I'm downloading XMLspy Home edition now to peek at it.  It's free for the entry level version.

The professional version single user is $500.  That is a few dollars but if it takes the dirty work out of this then it's worth it.  It's not highway robbery per se like what my former employer used to charge our clients for consulting. ($250/hr).  

It figures that Micro$oft makes no mention of XML Editor$ seeing as they don't make one (yet).  Sometimes we have to stop to put our shoes on before we run a race I guess.

>>Did you see anything in particular wrong with my ASP code and the stream object?<<

Here is some code I came up with that illustrates how you can use the Stream object in ADO. Do the following:

1. Create a stored procedure in the Northwind database as follows:

Create Procedure usp_GetXmlProducts

As

SET NOCOUNT ON

Select      1 As Tag,
      0 As Parent,
      Null as [Products!1],
      Null as [Product!2!ID],
      Null as [Product!2!Name],
      Null as [Product!2!SupplierID],
      Null as [Product!2!CategoryID],
      Null as [Product!2!Quantity],
      Null as [Product!2!UnitPrice]
Union
Select      2,
      1,
      Null,
      ProductID,
      ProductName,
      SupplierID,
      CategoryID,
      QuantityPerUnit,
      UnitPrice
From Products
For Xml Explicit
GO


2. Create the following ASP page:

<%
Dim cn, cmd, stm

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Add your coonection here to the Northwind database"

Set stm = Server.CreateObject("ADODB.Stream")
stm.Open

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
      Set .ActiveConnection = cn
      .CommandType = 4            'adCmdStoredProc
      .CommandText = "usp_GetXmlProducts"
      .Properties("Output Stream").Value = stm
      .Execute , , 1024                  'adExecuteStream
End With
Set cmd = Nothing

stm.SaveToFile "c:\Products.xml", 2                  ' adSaveCreateOverWrite
Response.Write "File saved."
stm.Close
Set stm = Nothing

cn.Close
Set cn = Nothing

%>

3. Run it.

I'm Running this code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/CatalogPipe.asp" -->
<% response.Buffer=true
Set happyrecords = Server.CreateObject("ADODB.command")
Set stm = Server.CreateObject("ADODB.stream")
happyrecords .ActiveConnection = MM_CatalogPipe_STRING
happyrecords .commandtype=4
happyrecords .commandtext = "dbo.amazon_xml_product_feed"
happyrecords .properties("Output Stream").value=stm
happyrecords .execute, , 1024

stm.savetofile "g:\test.xml",2
stm.close
set stm=nothing

And I'm getting this response.  
(response is emailed by server so format is a bit wierd at first glance)
An error occured on server=newww IP Address=192.168.1.40  Client IP=192.168.1.121  at 12/20/2004 11:50:22 PM BrowserType=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) Method=GET URL Called was: /xmlproduct.asp Query was:
|9|800a0cc1|Item_cannot_be_found_in_the_collection_corresponding_to_the_
|9|800a0cc1|re
quested_name_or_ordinal.
Path was: G:\wwwroot\xmlproduct.asp Referred by:
Error Message:
Category=ADODB.Command
Number=(0x800A0CC1)
Description=Item cannot be found in the collection corresponding to the requested name or ordinal.

Filename=/xmlproduct.asp
Number=9

>>What is an XLST transformation?<<
It is an Xml file, which at a minimum is used to transform one Xml document into another format (which could also be an Xml document).  As you can imagine it is far more complex than that and there is a many books on the subject.  Probably the best is XSLT 2.0 Programmer's Reference, 3rd Edition (WROX), it contains nearly 1,000 pages and if it does not have the answer to your question, it is not worth knowing.

>>It seems like the temp table is the most programmer friendly.  That allows me to chunk the query and run it in steps. <<
I agree. But you are still going to have a steep learning curve to get all the levels in the Xml document.

>>That is a few dollars but if it takes the dirty work out of this then it's worth it. <<
It will not help you much in this project.  It is mainly used for creating/validatating/transforming Xml documents.

>>It figures that Micro$oft makes no mention of XML Editor$ seeing as they don't make one (yet).<<
Actually they do it is called VS.NET

For one you missed a line, try it this way:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/CatalogPipe.asp" -->
<% response.Buffer=true
Set happyrecords = Server.CreateObject("ADODB.command")
Set stm = Server.CreateObject("ADODB.stream")
stm.Open           ' <-- Add this line
happyrecords .ActiveConnection = MM_CatalogPipe_STRING
happyrecords .commandtype=4
happyrecords .commandtext = "dbo.amazon_xml_product_feed"
happyrecords .properties("Output Stream").value=stm
happyrecords .execute, , 1024

stm.savetofile "g:\test.xml",2
stm.close
set stm=nothing

It seems like I could do this with a series of Nested While Do statements.

Am I wrong on that?   I know it's a really nasty resource cluster, and I did want to avoid that... but ...

This looks alot like the basic problem every programmin class touches on with nested for loops.

Maybe something like

For each product in products
    <sku />
   <taxcode />
  <descriptiion Data>
       <title / >
      <brand / >
     <giftwrap / >
     <giftmsg / >
    <discoverydata />
   </descriptiondata>
  <productData>
    <clothing>
       <variation data>
           <size / >
           <color / >
        </variation data>
        <classification data>
                 <clothing type / >
                 <dept (1-3)/>
                 <style (1.5)/>
                 <material/>
                 <occassion 1..3/>
                <event 1.5/>
       </classification>
      </clothing>
      </productdata>
    </product>
Next product


Wouldnt' that do it?

If so, why couldn't this be done with a cursor
Output the data as a series of rows (not necessarily in "XML") and then dump the whole thing to a file?

Maybe I'm just dog tired and hallucinating, but For XML explicit requires alot of crap, not SQL.


Let me regroup my remaining brain cells (that are still awake) and restate my idea for the record.

1. Use DTS job contiaining a SQL cursor loop.  Loop through the data and create the XML data with tags.
2. Store data in a temptable with one column as varchar (2500) (no field contains more then 300 characters.)
3. Store one element to a row.
4. When done, export data to a file with no delimiters and no excess data.

This should result in a XML file on disk right?  Hmm, I'm really tired.... off to sleep I go.. but poke holes at this idea.  This is really a kludgy way to work around my export issue... but I can't figure out why it wouldn't work.  It uses SQL's speed and data engine avoiding the ASP bottleneck and performance issues.  It chews up some space in the DB and results in some bad DB housekeeping, but that can be dropped or truncated later.  The end result should be a text file that strongly resembles XML or that can have a header/footer pasted to it to look right.

Btw, I appreciate you taking the time to respond and help me identify where the black holes in this are.

No, that is not the way I would go about.  Due to the complexity of the Xml document required, I would insert the necessary data in the expected format into a temporary table and then create a stored procedure using For Xml Explicit for the output.  I would then use ADO to execute the stored procedure and save the resulting Xml document to a file, either with VB, ASP, DTS, .NET or some other front end app that supports ADO.

I'm coding a proof of concept now.

My plan of attack is to use a cursor to enumerate the data and format the file.  That actually proves to be the easy part.
I'll then store the data 1 product to a row in a temp table.
That table will then be exported via DTS to a flat file.

A batch file will pick up the output and tack on the header/footer.

The net result should be well formed XML that is good for this specific situation.

Not ideal, but gets around the hassles I was having.  

I do something very similar to this for one of my other data feeds with a flat file for this vendor.  

I like this route because it's contained in SQL and thus can be linked to a trigger with the addition of a "do work " flag somewhere in the DB.  sort of a "if do flag is true then dump data".  Add the do flag set routine to the ASP that edits the DB.  Thus anytime someone updates the data the data gets pumped to disk and a batch file can run every fifteen minutes looking for work to do.  Or perhaps instead of a trigger the data can be harvested by running a SP via batch file every 30 min with the worker batch file running behind that.  This is primitive but keeps the data closely synchronized between our system and the  partner's system.

oh I reread my post and late-nite-i-sitis caused me to mistate the way I would store the data

One record per row.
Where record is a complete (iterated) product tag with subelements.

Basically harnessing SQL to build the structure then using batch files to tack on the data caps.

We can generate message ID with SQL and put another tidbit on the drive.

so it would look like

Headerstart+messageID+fluff_header+xml content+footer=complete.xml

where messagID and xml content are generated by the SQL Server.

IMHO my answer was the correct solution.

The project is still under development and that is why I have not closed the question.

My last post is the method that I chose to use.  

I anticipate being able to close the question within the next 20 to 30 days.  We are waiting on an opportunity to submit the test feed to the development system.  We did use the method that I outlined in my previous post where the XML data is built in SQL and then output using DTS to write a Unicode file. This is markedly different from ACPerkins suggestion, although his questions and responses were useful in arriving at the solution.

I'm using a stored procedure to create the XML and storing all components (header, data, and footer) as records in a table.  This is fast and efficient.

I'm then using DTS to output the file as a unicode file and using no delimiter for records and a CR for the record delimiter.  This appears to generate properly formed XML files in the unicode format we need.  It also makes a easily managed and completely SQL level process.  ADO is hideously slow and gets worse as you make the files larger.  I think at best this will be a partial award for that reason.

>>The project is still under development and that is why I have not closed the question.<<
I realize you are new here, but questions are considered abandoned after 21 days of inactivity.

>>I think at best this will be a partial award for that reason.<<
Please spare me,  I would rather not have my name associated to your "approach", it is neither scalable nor maintainable.

I wish you the best of luck.

Bob,

My (revised) recommendation: Please PAQ this question with a refund.

Your comments really give the wrong impression and don't seem terribly professional.  I get the impression in reading your comments that you are complaining that you want all the credit or none of the credit.  While your suggestions and responses were helpful, you had a bad attitude initially, and you consistently suggested ways that did not meet the stated requirement.  "cannot be done" is not a suggestion that counts.  I did, in the end, find a way to do what I wanted to do.  Your responses were helpful in eliminating development iterations looking for that solution and on XML in general.  However, I have a really difficult time giving you full credit because you have consistently been combative to the solution that does seem to be working and does meet the initial requirements laid out in my original post.

The lack of tact and professionalism in your approach to closing the question is a discredit to EE.  While your knowledge is helpful, I have graded you poorly because of your lack of tact and lack of professionalism.  It's frustrating to be in a position where I feel that this sort of post is helpful.  I'll put it bluntly:  You are talented, but if you were an employee of mine I would fire you over your attitude.

I did solve the problem myself, and my post earlier was an attempt to give some credit to you.

Bob - aka TheLearnedOne.  I'd really like to open a separate dialog with you regarding this.  As a subscribing member I have expectations of professionalism from posters who expect to receive credit.  Please email me directly so that we can discuss this.  The post is not the appropriate place for this discussion.

>>Your comments really give the wrong impression and don't seem terribly professional. <<
Thank you.  You have already pointed that out, right at the start of the thread.  It is a good thing you can't edit the feed back, isn't it?

>>"cannot be done" is not a suggestion that counts. <<
Not that I said that, but I am afraid you are wrong there. See here:
The correct answer to some questions is "you can't do that"
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi54

>>You are talented, but if you were an employee of mine I would fire you over your attitude.<<
Thank you, but that is why I am not.

>>As a subscribing member I have expectations of professionalism from posters who expect to receive credit.  Please email me directly so that we can discuss this. <<
The only expectations I have is that somebody who is asking for help (i.e. you) does not abandon the thread. ignores a reminder from a cleanup volunteer and only returns when a recomendation is made. But since you feel my comments, were inappropriate, feel free to post in Community Support.  I am sure Bob would feel better if a Moderator addressed this, instead.

>>The post is not the appropriate place for this discussion.<<
I agree.