XML straight through untouched

[nmarkroberts]

I've written a SQL stored procedure which produces the exact XML output that I require for an order export.

Now I want to make it easy to use.

Question 1: What's an easy way to do that?

Question 2:

I've tried wrapping it in a feed that the site admin can run and it will ftp the resulting file to where it's needed. So I need an XMLPackage which runs a simple query (calls the SP) and sends its XML output straight through to output without changing it.

I've tried a lot of different approaches and I'm getting nowhere; can anyone help please?

Here are the bare bones of the package:

<?xml version="1.0" standalone="yes"?>
<package debug="false">
  <query name="TheOrders" rowElementName="TheOrder" retType="xml">
     <sql>
       <![CDATA[ exec dbo.OrderExportNew ]]>
     </sql>
  </query>
 
  <PackageTransform>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
      <xsl:output method="xml" omit-xml-declaration="yes" encoding="utf-8"/>
      
<!--What do I put here?-->

    </xsl:stylesheet>
  </PackageTransform>
</package>

If I use this I get the result that I want but it's preceded by a couple of KB of rubbish that appears to be the entire state of the session.

      <xsl:template match="orders">
        <xsl:copy-of select="."/>
      </xsl:template>

This is the output from the SP:

<xml>
<orders>
<order>
  <orderId>100940</orderId>
  <debtor>Debtor TBA</debtor>
  <orderDate>2011-02-07T10:28:14.010</orderDate>
  <dueDate>14 February 2011</dueDate>
  <lineItem>
    <itemCode>S10191 - TBS</itemCode>
    <quantity>6</quantity>
  </lineItem>
</order>
</orders>
</xml>

[nmarkroberts]

Feeling lonely here!

The package I tried works, but spews heaps of random unstructured data at the beginning of the output, how do I prevent that?

[BFG 9000]

Have you tried calling it as http://www.site.com/x-xmlpackagename.aspx


TTFN

BFG

[nmarkroberts]

Yes, with largely the same result.

The SP returns the following (rather dodgy) XML formatted as required for import to another package:

<xml><orders><order><orderID>100944</orderID><debtor>IntSalEPar</debtor><memberNo/><orderDate>13 Feb 2011</orderDate><dueDate>21 February 2011</dueDate><nameAddr>kjhkjhkjhkjh  Gunnedah, NSW 2380  0267422453</nameAddr><lineItems><lineItem><itemCode>S1004 - TBS</itemCode><quantity>10</quantity></lineItem></lineItems></order></orders></xml>

The XML Package does this to it (I have removed / masked some of the detail):

True1621771110FredBloggsFred BloggsFree,Admin,SuperAdmin,NoSubscription,Registered,S10181 - TBSTrueTruefalsefalseen-AUAUDen-AUen-USAUD2/16/201111:18 AM10feeds.aspx/sbadmin/feeds.aspxfeed.orderexportnew.xml.confighttps://ishop.website.com.au/Wed, 16 Feb 2011 01:18:03 GMTtruefeeds.aspx/wEPDwUJMjU4ODIwNzQwD2QWAgIDD2QWAgIDD2QWAgIDDxYCHgtfIUl0ZW1Db3VudAICFgQCAQ9kFgxmDxUBATFkAgEPDxYEHgtOYXZpZ2F0ZVVybAUWZWRpdGZlZWQuYXNweD9mZWVkaWQ9MR4EVGV4dAUKR29vZ2xlQmFzZWRkAgIPFQEaZmVlZC5nb29nbGViYXNlLnhtbC5jb25maWdkAgMPDxYCHg1PbkNsaWVudENsaWNrBRpFZGl0RmVlZCgxKTsgcmV0dXJuIGZhbHNlO2RkAgUPDxYCHg9Db21tYW5kQXJndW1lbnQFATFkZAIHDw8WAh8EBQExFgIeB29uQ2xpY2sFSGphdmFzY3JpcHQ6IHJldHVybiBjb25maXJtKCdBcmUgeW91IHN1cmUgeW91IHdhbnQgdG8gZGVsZXRlIHRoaXMgZmVlZD8nKWQCAg9kFgxmDxUBATRkAgEPDxYEHwEFFmVkaXRmZWVkLmFzcHg/ZmVlZGlkPTQfAgURTmV3IE9yZGVycyBFeHBvcnRkZAICDxUBHmZlZWQub3JkZXJleHBvcnRuZXcueG1sLmNvbmZpZ2QCAw8PFgIfAwUaRWRpdEZlZWQoNCk7IHJldHVybiBmYWxzZTtkZAIFDw8WAh8EBQE0ZGQCBw8PFgIfBAUBNGRkZC1ygPLA9dUB1ePBpm3onzygS+7h1245699356.321014303.1292813082.1297662683.1297678116.105245699356.1297034457.83.9.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=organic sourdough bread kamut spelt ryeChart40011010110000000B606992AA909EED47E6415B57173FFCD7E7A3CAD30FF9978F42D51526E23146E7614024052E27A816D5AB99D3AD9BA58EE4133B82FAC4E28DBF43C572C9852FCDE52CB8CC1A0B971B965F4785E760A07C0E9D52225AD4F6853FB13F1FDAD5F570D5BBE74017F8D4EA4B8C051EBE3EF2DB21A24E031CF9626D5B93D57614A572BCA771DFFA4751C15701F7531446C5EEC4E3A92338B3A85AEB50DC5002AD77C843D1AED0D7F535B94F7C5FD76910DA8B81001F8AAishop.website.com.auMozilla/5.0 (Windows; U; Windows NT 6.0; en-GB; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13 ( .NET CLR 3.5.30729; .NET4.0E)Forms81292a37-e058-4e3e-a236-a1321ded1013on192.168.250.30/sbadmin/feeds.aspxD:\Websites\LocalUser\ishop_website\sbadmin\feeds.aspx/sbadmin/feeds.aspxishop.website.com.au158.106.10.175AUDTruehttps://ishop.website.com.au/Falsefeeds.aspxTrueBloggsAUDen-AU17False10true1en-AU/sbadmin/feeds.aspx01en-US1True2/16/2011Free,Admin,SuperAdmin,NoSubscription,Registered,S10181 - TBSFalse11:18 AMFredfeed.orderexportnew.xml.config1Fred Bloggs1

<orders><order><orderID>100944</orderID><debtor>IntSalEPar</debtor><memberNo /><orderDate>13 Feb 2011</orderDate><dueDate>21 February 2011</dueDate><nameAddr>gfjhgjghj
Gunnedah, NSW 2380
</nameAddr><lineItems><lineItem><itemCode>S1004 - TBS</itemCode><quantity>10</quantity></lineItem></lineItems></order><order></orders>

Note that the XML is preceded by lots of interesting stuff and the <xml> tags have gone missing.