Exporting XML file format issue

Hello
I am trying to generate an XML file from SQL Server which gets pushed out to
a third party app using DTS. So Far I have created a sproc like so.
CREATE PROC querystrCR
AS
SELECT 1 as Tag,
NULL as Parent,
'<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
'<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
'<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
'<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
'<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
'<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
'<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
FROM CR
FOR XML EXPLICIT
It needs the CDATA fields as they contain several odd characters. Then I
export the file as part of a DTS package like so;
EXEC sp_makewebtask
@.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
@.query = 'EXEC querystrCR',
@.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
The template file looks like
<?xml version="1.0" encoding="utf-8" ?>
<DB>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</DB>
The problem is with the format of the output file. SQL Server spits it out
in rows which don't correspond to the tags e.g. a line end like
</CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
Obviously this means the third party app. will not parse it and a web
browser will not open the file e.g. Firefox reports "XML Parsing Error: not
well-formed". I need the output in the exported file to look
<CPB>
<CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
<CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
<CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
<CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
<CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
<PRIO><![CDATA[ "1" ]]></PRIO>
<MTIME><![CDATA[ "999999999" ]]></MTIME>
</CPB>
Any Help would be appreciated.
Cheers
Matt
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
A couple of points:
1. Instead of manually constructing the <![CDATA[ use the !cdata directive.
2. However, the CDATA section should not give you anything that you cannot
achieve otherwise. In particular it does not help with invalid characters
(what are your odd characters?). So you may not need to use it.
3. FOR XML results per default return fragments. In order to export it as a
document, add the root node (there are a couple of ways depending on the API
used).
4. If you want to expose the XML, you should use either the respective
stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked XML
in approx 4kBytes blocks that you need to merge yourself.
HTH
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:4207e650$1@.news01.wxnz.net...
> Hello
> I am trying to generate an XML file from SQL Server which gets pushed out
> to a third party app using DTS. So Far I have created a sproc like so.
> CREATE PROC querystrCR
> AS
> SELECT 1 as Tag,
> NULL as Parent,
> '<![CDATA[' + CR_NAME + ']]>' as [CR!1!CR_NAME!XML],
> '<![CDATA[' + CR_SRC + ']]>' as [CR!1!CR_SCR!XML],
> '<![CDATA[' + CR_FLAGS + ']]>' as [CR!1!CR_FLAGS!XML],
> '<![CDATA[' + CR_STIME + ']]>' as [CR!1!CR_STIME!XML],
> '<![CDATA[' + CR_FTIME + ']]>' as [CR!1!CR_FTIME!XML],
> '<![CDATA[' + PRIO + ']]>' as [CR!1!PRIO!XML],
> '<![CDATA[' + MTIME + ']]>' as [CR!1!MTIME!XML]
> FROM CR
> FOR XML EXPLICIT
>
> It needs the CDATA fields as they contain several odd characters. Then I
> export the file as part of a DTS package like so;
> EXEC sp_makewebtask
> @.outputfile = 'e:\CDR\Routing\cuscall-callroute.xml',
> @.query = 'EXEC querystrCR',
> @.templatefile = 'e:\CDR\Routing\cuscallCR.tpl'
> The template file looks like
> <?xml version="1.0" encoding="utf-8" ?>
> <DB>
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> </DB>
> The problem is with the format of the output file. SQL Server spits it out
> in rows which don't correspond to the tags e.g. a line end like
> </CR_STIME><CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FT
> Obviously this means the third party app. will not parse it and a web
> browser will not open the file e.g. Firefox reports "XML Parsing Error:
> not well-formed". I need the output in the exported file to look
> <CPB>
> <CR_NAME><![CDATA[ "CTE-CLI Active" ]]></CR_NAME>
> <CR_SRC><![CDATA[ "RES-062728464" ]]></CR_SRC>
> <CR_FLAGS><![CDATA[ "0" ]]></CR_FLAGS>
> <CR_STIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_STIME>
> <CR_FTIME><![CDATA[ "-1/-1/-1/-1/-1/-1/-1/-1" ]]></CR_FTIME>
> <PRIO><![CDATA[ "1" ]]></PRIO>
> <MTIME><![CDATA[ "999999999" ]]></MTIME>
> </CPB>
> Any Help would be appreciated.
> Cheers
> Matt
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>
|||I have everything I want in my XML file the only thing I need to format now
is the CR. I know this because when I open the example XML that the third
party app will parse, in notepad I see the square cube CR character. If I
manually edit my XML file in notepad and "paste" this char in after every
closing TAG the XML I generated is parsed and imported to the app.
I'm pulling the XML over HTTP using this script; Can I format this XML here?
var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
xml.async = false;
xml.load("http://localhost/routingtwo/template/test.xml");
xml.save("testoutput.xml")
or can I do it in my template test.xml
<?xml version="1.0" encoding="UTF-8"?>
<CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
exec CSM.dbo.querystrCR
</sql:query>
</CSM>
or is it in the actual SQL query?
Cheers
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>A couple of points:
> 1. Instead of manually constructing the <![CDATA[ use the !cdata
> directive.
> 2. However, the CDATA section should not give you anything that you cannot
> achieve otherwise. In particular it does not help with invalid characters
> (what are your odd characters?). So you may not need to use it.
> 3. FOR XML results per default return fragments. In order to export it as
> a document, add the root node (there are a couple of ways depending on the
> API used).
> 4. If you want to expose the XML, you should use either the respective
> stream-based APIs (ADO/OLEDB CommandStream, the SQLXML classes in ADO.net)
> or use the SQLXML HTTP ISAPI. Otherwise (e.g. ODBC), you may get chunked
> XML in approx 4kBytes blocks that you need to merge yourself.
> HTH
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:4207e650$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
|||Why would you want a carriage return added? An XML parser always replaces a
CR (or a CR/LF sequence) with a LF. So where do you need to send the CR to?
Does it also work without the CR present?
Thanks
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420a7ee3$1@.news01.wxnz.net...
>I have everything I want in my XML file the only thing I need to format now
>is the CR. I know this because when I open the example XML that the third
>party app will parse, in notepad I see the square cube CR character. If I
>manually edit my XML file in notepad and "paste" this char in after every
>closing TAG the XML I generated is parsed and imported to the app.
> I'm pulling the XML over HTTP using this script; Can I format this XML
> here?
> var xml= new ActiveXObject("Msxml2.DomDocument.4.0");
> xml.async = false;
> xml.load("http://localhost/routingtwo/template/test.xml");
> xml.save("testoutput.xml")
> or can I do it in my template test.xml
> <?xml version="1.0" encoding="UTF-8"?>
> <CSM xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> <sql:query>
> exec CSM.dbo.querystrCR
> </sql:query>
> </CSM>
> or is it in the actual SQL query?
> Cheers
> Matt
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ukLCbiWDFHA.3840@.tk2msftngp13.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>
|||My error it was an encoding problem - I was saving my XML template as ANSI
not Unicode.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Why would you want a carriage return added? An XML parser always replaces
> a CR (or a CR/LF sequence) with a LF. So where do you need to send the CR
> to? Does it also work without the CR present?
> Thanks
> Michael
> "Matt" <korf@.xnet.co.nz> wrote in message
> news:420a7ee3$1@.news01.wxnz.net...
>
x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
|||Cool (well, now it is :-)).
Best regards
Michael
"Matt" <korf@.xnet.co.nz> wrote in message news:420be8dd$1@.news01.wxnz.net...
> My error it was an encoding problem - I was saving my XML template as ANSI
> not Unicode.
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eBfQetyDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
> x-- 100 Proof News - http://www.100ProofNews.com
> x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
> x-- Access to over 1 Terabyte per Day - $8.95/Month
> x-- UNLIMITED DOWNLOAD
>