Question

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

Asked by: NTJOCK

I'm having difficulty exporting XML from SQL Server 2000.

I have the select statement written. It's not finished, but it is generating XML.  In order to tune the structure of the select statement I need to get the data into a browser or text file.  When SQL generates XML it returns blocks of 8192 characters in a universal table.  This is terrible to read and doesn't have a root element.

My problem is that every time I try to export the data it either doesn't work, or returns an error.

I was going to do this with a DTS package but the wizard chokes when it sees the For XML clause.  It returns an error that states XML is prohibited in DTS packages or something very similar to that.
I tried hiding the XML in a Stored Procedure, same message.

I also tried using an ASP  page to fetch the contents into a variable that I could manipulate.  The ASP stream object seems very complicated and it's cumbersome to schedule ASP pages to run.  I also don't like the security repurcussions of having a login somewhere to run a piece of code in IE.  

The desired solution is a DTS package that will generate the XML and export it to a file.  The file then can be touched up (add a header and footer) and moved to the appropriate directory for processing.  I'm working on the first file of 6 that belong to a particular vendor's feed.  These files will be generated at least once per day and sent to the vendor/partner to keep their system in synch.  Thus a scheduled DTS package that outputs a file is a manageable and automatable solution.

The tactic I'm trying now is storing the XML into a table and then exporting the contents of the table.  I'm having a really hard time putting a handle on the data from within SQL.  I've also ruled out Xpath because the DB resides on a production web server and that raises an unacceptable risk.  

I'm open to other ideas as long as they conform to using a DTS package and a SQL centric solution.  Microsoft's documentation suggests that this is possible, but everything I can find now points to .net.  The books online says this can be done, but I can't get it to work.  I'm hoping a fellow EE member can help me see the light in time to enjoy a Xmas break.  :)

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2004-12-20 at 19:30:12ID21249298
Tags

xml

Topics

MS SQL Server

,

Extensible HTML (XHTML)

Participating Experts
2
Points
200
Comments
35

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. FOR XML EXPLICIT
    Hello experts, I am having a problem with SELECT TOP X queries in FOR XML EXPLICIT format. I want to return the top 300 products in my catalog. What happens is that each product has descriptions in several languages, which makes up an XML Schema like this: <PRODUCT...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: acperkinsPosted on 2004-12-20 at 19:39:38ID: 12872891

>>When SQL generates XML it returns blocks of 8192 characters in a universal table.  <<
Huh? Are you not confusing the results produced by SQL Query Analyzer with SQL Server?

>>doesn't have a root element.<<
If you are talking about For Xml Explicit, than that it is quite simply incorrect.

>>The ASP stream object seems very complicated and it's cumbersome to schedule ASP pages to run.<<
It is not complicated at all.  Unless you consider an extra pair of lines complicated.

>>I'm having a really hard time putting a handle on the data from within SQL.<<
Let's put it this way, if you considered the Stream object complcated, then this is simply out of the question.  Your best bet is to return it to a client, such as ASP.

>> I've also ruled out Xpath because the DB resides on a production web server and that raises an unacceptable risk. <<
I suspect you are confusing XPath with SQL Xml.  They are significantly different animals.

 

by: NTJOCKPosted on 2004-12-20 at 19:57:25ID: 12872955

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.

 

by: NTJOCKPosted on 2004-12-20 at 20:06:17ID: 12872994

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

 

by: acperkinsPosted on 2004-12-20 at 20:16:04ID: 12873035

>>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.

 

by: acperkinsPosted on 2004-12-20 at 20:23:47ID: 12873176

>>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.

 

by: acperkinsPosted on 2004-12-20 at 20:25:48ID: 12873186

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

 

by: NTJOCKPosted on 2004-12-20 at 20:31:06ID: 12873212

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.  

 

by: NTJOCKPosted on 2004-12-20 at 20:36:18ID: 12873222

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.

 

by: NTJOCKPosted on 2004-12-20 at 20:38:26ID: 12873226

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>

 

by: NTJOCKPosted on 2004-12-20 at 20:46:31ID: 12873254

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...

 

by: acperkinsPosted on 2004-12-20 at 20:51:27ID: 12873283

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>

 

by: NTJOCKPosted on 2004-12-20 at 20:51:42ID: 12873284

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.

 

by: NTJOCKPosted on 2004-12-20 at 20:57:33ID: 12873303

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.

 

by: NTJOCKPosted on 2004-12-20 at 21:02:18ID: 12873319

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.

 

by: acperkinsPosted on 2004-12-20 at 21:10:15ID: 12873348

>>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.

 

by: NTJOCKPosted on 2004-12-20 at 21:30:42ID: 12873419

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

 

by: NTJOCKPosted on 2004-12-20 at 21:37:41ID: 12873444

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.

 

by: acperkinsPosted on 2004-12-20 at 21:44:24ID: 12873469

>>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.

 

by: NTJOCKPosted on 2004-12-20 at 21:53:16ID: 12873504

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


 

by: acperkinsPosted on 2004-12-20 at 21:55:48ID: 12873511

>>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

 

by: acperkinsPosted on 2004-12-20 at 21:59:03ID: 12873523

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

 

by: NTJOCKPosted on 2004-12-20 at 22:07:28ID: 12873542

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.

 

by: acperkinsPosted on 2004-12-21 at 07:10:04ID: 12876242

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.

 

by: NTJOCKPosted on 2004-12-21 at 07:18:38ID: 12876318

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.

 

by: NTJOCKPosted on 2004-12-21 at 07:21:29ID: 12876345

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.

 

by: acperkinsPosted on 2005-01-17 at 18:48:56ID: 13069118

IMHO my answer was the correct solution.

 

by: NTJOCKPosted on 2005-01-18 at 05:37:22ID: 13071879

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.  

 

by: NTJOCKPosted on 2005-01-18 at 05:44:41ID: 13071962

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.

 

by: acperkinsPosted on 2005-01-18 at 07:02:01ID: 13072696

>>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.

 

by: NTJOCKPosted on 2005-01-18 at 07:23:15ID: 13072935

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.

 

by: acperkinsPosted on 2005-01-18 at 09:26:31ID: 13074485

>>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.

 

by: ee_ai_constructPosted on 2005-01-22 at 10:53:12ID: 13111631

Points reduced from 500 to 200 for close.

ee_ai_construct (replacement part #xm34)
Community Support Admin

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...