Extract data from SQL Server 2005 by SMO or DMO

I am running an old script generator using SQL-DMO. Even on SQL Server 2005 it is working fine, but the new features like xml data type are not supported. So I switched to SMO. At the first view it looks pretty cool and easy. I changed the properties in the following source a thousand times but it doesn’t script any data to the file. Is it a bug or a stupid misunderstanding?

Transfer t = new Transfer(db);

t.CopyAllObjects = false;

t.CopyAllTables = true;

t.CopyData = true;

//t.Options.WithDependencies = true;

t.Options.ContinueScriptingOnError = true;

t.DestinationServer = "PC-E221\\SQLEXPRESS";

t.DestinationDatabase = "TestAgent";

t.DestinationLoginSecure = true;

t.CreateTargetDatabase = true;

t.Options.AllowSystemObjects = false;

t.Options.FileName = "testFile.sql";

t.Options.IncludeDatabaseContext = true;

t.Options.ToFileOnly = true;

Best regards

Wolfgang

Smo is a tool for generating scripts / manitaining the database not scripting the data out.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

thanks for your response.

1. If it is so, what does ".CopyData= true" mean, if it doesn't copy data?

2. How can I copy data, if DMO doesn't work either? As I said before, DMO doesn't copy xml data types.

br

Wolfgang

|||1. That is related to the TransferData method which will use DTS behind the scenes to transfer the data (read this somewhere sometime).

2. You could use a scripting utility like this here: http://vyaskn.tripod.com/code.htm to do the job. i don′t know if this is capable of using XMlL txypes, but its worth a try, because it can be really quick tested.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito CorleoneAre you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito Corleone
Are you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone
sql

Extract a complete XML section and sub sections

Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
You can specify relative XPaths for the columns in the subelements, as shown
below. Is that what you mean?
DECLARE @.TESTXML nvarchar(2000)
DECLARE @.hDoc integer
SET @.TESTXML =
'<Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>'
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header', 1)
with
(reportType varchar(10),
reportNumber VarChar(6),
batchNumber varchar(6),
reportSequenceNumber varchar(6),
userNumber varchar(6),
ProducedOnTime nvarchar(10) 'ProducedOn/@.time',
ProducedOnDate nvarchar(20) 'ProducedOn/@.date',
ProcessingDate nvarchar(20) 'ProcessingDate/@.date' )
EXEC sp_xml_removedocument @.hDoc
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4CD137BC-09E8-42A8-BC86-6C6991EA979C@.microsoft.com...
Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
sql

Extra xml node

This is in sql 2005
I have a query that is returning a set of rows each with one xml field
row1 -<apple></apple>
row3 -<orange></orange>
row2-<grape></grape>
I want the output to look like this
<fruits>
<apple></apple>
<orange></orange>
<grape></grape>
</fruits>
However when I use FOR XML AUTO, root(''Fruits'')
<fruits>
<fruit><apple></apple></fruit>
<fruit><orange></orange></fruit>
<fruit> <grape></grape></fruit>
</fruits>
How do I remove the extra fruit element?
ENDHello Hyper,
Try using a FOR XML PATH query instead, ala for xml path (''),root('fruits')
,type
Thanks!
Kent

> This is in sql 2005
> I have a query that is returning a set of rows each with one xml field
> row1 -<apple></apple>
> row3 -<orange></orange>
> row2-<grape></grape>
> I want the output to look like this
> <fruits>
> <apple></apple>
> <orange></orange>
> <grape></grape>
> </fruits>
> However when I use FOR XML AUTO, root(''Fruits'')
> <fruits>
> <fruit><apple></apple></fruit>
> <fruit><orange></orange></fruit>
> <fruit> <grape></grape></fruit>
> </fruits>
> How do I remove the extra fruit element?
> END
>
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

external access denied to update a config file

Hello,

I'm having an issue with a CLR Stored procedure. Everything works great in a 32 bit environment, I have a CLR SP that updates an xml file stored on a local drive. When I execute the Stored proc it does go and update what I want it to in a 32 bit system. When I run the CLR SP on a 64 bit cluster, I seem to have give the "everyone" group write permissions to my G: drive (which is where the file is located that I'm updating). What security context is this SP running under? I thought it would be either under the SQL Service account (which is a domain user in the local administrators group) or what I'm logged in as when I run it from Management Studio (which is a domain admin, also in the local administrators group). If I have given the local administrators group "Full Control" access to the G: drive, why isn't this enough? Why do I have to give the Everyone group write access?

The security context seems odd to me, it seems like it's not running as either one of those 2 users I mentioned, because if it was, then it should be able to update the xml file.

Any help appreciated. Here's the error I'm getting:

Msg 6522, Level 16, State 1, Procedure usp_XMLWriter, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_XMLWriter':

System.UnauthorizedAccessException: Access to the path 'g:\ssisPackages\BuildCalendar\andy.dtsConfig' is denied.

System.UnauthorizedAccessException:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)

at System.Xml.XmlTextWriter..ctor(String filename, Encoding encoding)

at System.Xml.XmlDocument.Save(String filename)

at XMLWriter.StoredProcedures.usp_XMLWriter(String xmlDocPath, String xmlNodePath, String xmlInnerText)

.

I found out that I'm able to get this to work if I put the SQL Service User account directly into the security of the G: Drive. For some reason, even though the user is in the Local Administrators group, it doesn't seem to pick up the fact that my user in is in the group, and therefore won't let him update a file in the folder.

Does anyone know of any issues in a 64 bit cluster where a CLR Stored proc isn't able to decipher the users in a local Group?

|||This turned out to be a cluster issue. We weren't doing permissions through the cluster, we were doing permissions to the file shares through each node, which is a mistake in a cluster.

extending RS for Connection String only

I know there have been many questions regarding extending RS to report off of
different data sources (xml, ado etc) - we are successfully running our
reports off of SQL Server stored procedures and are using the web services
(not url) to render directly to PDF from a custom asp .net UI. We would like
to leave this as is, tying the RS Datasource to a SQL Server stored procedure
and leaving it at that. Is it possible to use only the connection interfaces
(IDbConnection) in conjunction with the web services to programmatically
switch between datasources? Or do you have to implement the whole nine yards
(IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
custom dataset in order to switch the connection string?
Thanks,ok, I realize maybe that was a stupid question. Here is another one. If we
need to create a custom data extension in order to manipulate the connection
string, is it possible to create a 'custom' data extension that executes SQL
Stored Procedures using the .net SqlCommand for its 'custom data', seeing as
how we have the reports up and running using the stored procedures?
I am trying desperately to figure out the best approach to take. We need to
be able to switch to one of 60 + databases depending on the user requesting
the report. The custom code is in place, the reports done and working - what
would anyone suggest would be the best way to accomplish the last piece of
this puzzle?
"Myles" wrote:
> I know there have been many questions regarding extending RS to report off of
> different data sources (xml, ado etc) - we are successfully running our
> reports off of SQL Server stored procedures and are using the web services
> (not url) to render directly to PDF from a custom asp .net UI. We would like
> to leave this as is, tying the RS Datasource to a SQL Server stored procedure
> and leaving it at that. Is it possible to use only the connection interfaces
> (IDbConnection) in conjunction with the web services to programmatically
> switch between datasources? Or do you have to implement the whole nine yards
> (IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
> custom dataset in order to switch the connection string?
> Thanks,

Exporting XML using bcp

I am exporting XML query results from a stored procedure using bcp. The query is pulling data from several tables and create XML using FOR XML EXPLICIT.

The following is the bcp command used to export the result:

bcp "exec sprocname" queryout output.xml -w -T

The resulting output file appears to limit the number of charcters per line to 2033 characters and cut the data into the next row regardless whether the 2033th character is in the middle of an xml tag. As a result the resulting xml output become unreadable.

Any suggestions would be much appreciated.

I hope you are using SQL2005. If so, just add ,TYPE at the end of your FOR XML query as

SELECT ...

FOR EXPLICIT, TYPE

Exporting XML using bcp

I am exporting XML query results from a stored procedure using bcp. The query is pulling data from several tables and create XML using FOR XML EXPLICIT.

The following is the bcp command used to export the result:

bcp "exec sprocname" queryout output.xml -w -T

The resulting output file appears to limit the number of charcters per line to 2033 characters and cut the data into the next row regardless whether the 2033th character is in the middle of an xml tag. As a result the resulting xml output become unreadable.

Any suggestions would be much appreciated.

I hope you are using SQL2005. If so, just add ,TYPE at the end of your FOR XML query as

SELECT ...

FOR EXPLICIT, TYPE

Exporting XML to a file

Is there a way to get BCP or a sql store procedure to export my database to
a
XML file ?You may use FOR XML queries to export you all db as XML.
"rseedle" <rseedle@.discussions.microsoft.com> wrote in message
news:35182A41-7226-4D91-84B7-576301CBB400@.microsoft.com...
> Is there a way to get BCP or a sql store procedure to export my database
> to a
> XML file ?|||I guess what I really want to know is, how do I get the results of the selec
t
... for XML to a file. Getting it to display on the screen is nice but I
really needs it to be in a file.
Randy
"Bertan ARI [MSFT]" wrote:

> You may use FOR XML queries to export you all db as XML.
>
> "rseedle" <rseedle@.discussions.microsoft.com> wrote in message
> news:35182A41-7226-4D91-84B7-576301CBB400@.microsoft.com...
>
>|||In addition, when I run the query:
select * from Users for xml auto
or
select * from Users for xml raw
Query analyzer truncates the text that gets displayed. So if I do tell it to
save it to a file in Query Analyzer the XML data is truncated.
"rseedle" wrote:
> I guess what I really want to know is, how do I get the results of the sel
ect
> ... for XML to a file. Getting it to display on the screen is nice but I
> really needs it to be in a file.
> Randy
> "Bertan ARI [MSFT]" wrote:
>|||You have to write a small APP that will execute your query and store the
results in a file stream.
"rseedle" <rseedle@.discussions.microsoft.com> wrote in message
news:8A27F4A1-0D39-47E0-A4CE-E77EC2ECED58@.microsoft.com...
> In addition, when I run the query:
> select * from Users for xml auto
> or
> select * from Users for xml raw
> Query analyzer truncates the text that gets displayed. So if I do tell it
> to
> save it to a file in Query Analyzer the XML data is truncated.
>
> "rseedle" wrote:
>

Exporting XML to a file

Is there a way to get BCP or a sql store procedure to export my database to a
XML file ?
You may use FOR XML queries to export you all db as XML.
"rseedle" <rseedle@.discussions.microsoft.com> wrote in message
news:35182A41-7226-4D91-84B7-576301CBB400@.microsoft.com...
> Is there a way to get BCP or a sql store procedure to export my database
> to a
> XML file ?
|||I guess what I really want to know is, how do I get the results of the select
... for XML to a file. Getting it to display on the screen is nice but I
really needs it to be in a file.
Randy
"Bertan ARI [MSFT]" wrote:

> You may use FOR XML queries to export you all db as XML.
>
> "rseedle" <rseedle@.discussions.microsoft.com> wrote in message
> news:35182A41-7226-4D91-84B7-576301CBB400@.microsoft.com...
>
>
|||In addition, when I run the query:
select * from Users for xml auto
or
select * from Users for xml raw
Query analyzer truncates the text that gets displayed. So if I do tell it to
save it to a file in Query Analyzer the XML data is truncated.
"rseedle" wrote:
[vbcol=seagreen]
> I guess what I really want to know is, how do I get the results of the select
> ... for XML to a file. Getting it to display on the screen is nice but I
> really needs it to be in a file.
> Randy
> "Bertan ARI [MSFT]" wrote:
|||You have to write a small APP that will execute your query and store the
results in a file stream.
"rseedle" <rseedle@.discussions.microsoft.com> wrote in message
news:8A27F4A1-0D39-47E0-A4CE-E77EC2ECED58@.microsoft.com...[vbcol=seagreen]
> In addition, when I run the query:
> select * from Users for xml auto
> or
> select * from Users for xml raw
> Query analyzer truncates the text that gets displayed. So if I do tell it
> to
> save it to a file in Query Analyzer the XML data is truncated.
>
> "rseedle" wrote:

Exporting XML file from SQL Server using FOR XML AUTO

Hello All,
I'm trying to export XML using osql or the sp_makewebtask and having
problems having IE 6.0 read the outputted format. The file is outputted
contains control line feeds in the middle of a row causing IE to throw up
errors. Is there a way to avoid these random control linefeeds in the file?
Any help is greatly appreciated.
Thanks in advance,
Frank
Hi Frank,
We have experienced this problem also and found out that there is actually a bug with FOR...XML statements when executed over ODBC. As you've discovered, it only returns 2083 characters per line.
Here is the link to the KB article:
http://support.microsoft.com/default...;en-us;Q275583
If you do a "Search By Author" on my username, you can find a list of some other posts I've made regarding this issue one of which discusses a workaround using OLE DB and DTS.
HTH,
Denise E. White
Technical Director
The Next Version Ltd. UK
www.thenextversion.com
www.denisewhite.co.uk
-- Frank DeLuccia wrote: --
Hello All,
I'm trying to export XML using osql or the sp_makewebtask and having
problems having IE 6.0 read the outputted format. The file is outputted
contains control line feeds in the middle of a row causing IE to throw up
errors. Is there a way to avoid these random control linefeeds in the file?
Any help is greatly appreciated.
Thanks in advance,
Frank
|||Hi...
My solutions... after 3 weeks....
PROCEDURE XXX
AS
SET NOCOUNT ON
CREATE TABLE ##Tmp_OTs
(
WoXML nvarchar(200)
)
INSERT INTO ##Tmp_OTs VALUES (HTMLFILE)
INSERT INTO ##Tmp_OTs VALUES ('<XML id="xmlWO" version="1.0"
encoding="ISO-8859-1">')
INSERT INTO ##Tmp_OTs VALUES ('<OTS>')
INSERT INTO ##Tmp_OTs
SELECT ( CREATE XML FORMAT BY RECORD)
'<OT>' +
'<A>' + FIELD1 + '</A>' +
'</OT>'
FROM TABLE
INSERT INTO ##Tmp_OTs VALUES ('</OTS>')
INSERT INTO ##Tmp_OTs VALUES ('</XML>')
EXEC sp_makewebtask
@.outputfile = 'OUTPUT FILE',
@.query = 'SELECT * FROM ##Tmp_OTs',
@.templatefile = 'TEPLATE FILE'
*************************************************
TEMPLATE FILE
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
I'm sorry my english not good.
Regards
Don Cata
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
> Hi Frank,
> We have experienced this problem also and found out that there is actually
a bug with FOR...XML statements when executed over ODBC. As you've
discovered, it only returns 2083 characters per line.
> Here is the link to the KB article:
> http://support.microsoft.com/default...;en-us;Q275583
> If you do a "Search By Author" on my username, you can find a list of some
other posts I've made regarding this issue one of which discusses a
workaround using OLE DB and DTS.
> HTH,
> Denise E. White
> Technical Director
> The Next Version Ltd. UK
> www.thenextversion.com
> www.denisewhite.co.uk
>
> -- Frank DeLuccia wrote: --
> Hello All,
> I'm trying to export XML using osql or the sp_makewebtask and
having
> problems having IE 6.0 read the outputted format. The file is
outputted
> contains control line feeds in the middle of a row causing IE to
throw up
> errors. Is there a way to avoid these random control linefeeds in
the file?
> Any help is greatly appreciated.
> Thanks in advance,
> Frank
>
>
|||Thanks for the help!!
"DonCata" <cavelardo@.hotmail.com> wrote in message
news:%23EfO7xZSEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi...
> My solutions... after 3 weeks....
> PROCEDURE XXX
> AS
> SET NOCOUNT ON
> CREATE TABLE ##Tmp_OTs
> (
> WoXML nvarchar(200)
> )
> INSERT INTO ##Tmp_OTs VALUES (HTMLFILE)
> INSERT INTO ##Tmp_OTs VALUES ('<XML id="xmlWO" version="1.0"
> encoding="ISO-8859-1">')
> INSERT INTO ##Tmp_OTs VALUES ('<OTS>')
> INSERT INTO ##Tmp_OTs
> SELECT ( CREATE XML FORMAT BY RECORD)
> '<OT>' +
> '<A>' + FIELD1 + '</A>' +
> '</OT>'
> FROM TABLE
> INSERT INTO ##Tmp_OTs VALUES ('</OTS>')
> INSERT INTO ##Tmp_OTs VALUES ('</XML>')
> EXEC sp_makewebtask
> @.outputfile = 'OUTPUT FILE',
> @.query = 'SELECT * FROM ##Tmp_OTs',
> @.templatefile = 'TEPLATE FILE'
>
> *************************************************
> TEMPLATE FILE
> <%begindetail%>
> <%insert_data_here%>
> <%enddetail%>
> I'm sorry my english not good.
> Regards
> Don Cata
> "mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
> news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
actually[vbcol=seagreen]
> a bug with FOR...XML statements when executed over ODBC. As you've
> discovered, it only returns 2083 characters per line.
some
> other posts I've made regarding this issue one of which discusses a
> workaround using OLE DB and DTS.
> having
> outputted
> throw up
> the file?
>
|||Thanks for the help!!!
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
> Hi Frank,
> We have experienced this problem also and found out that there is actually
a bug with FOR...XML statements when executed over ODBC. As you've
discovered, it only returns 2083 characters per line.
> Here is the link to the KB article:
> http://support.microsoft.com/default...;en-us;Q275583
> If you do a "Search By Author" on my username, you can find a list of some
other posts I've made regarding this issue one of which discusses a
workaround using OLE DB and DTS.
> HTH,
> Denise E. White
> Technical Director
> The Next Version Ltd. UK
> www.thenextversion.com
> www.denisewhite.co.uk
>
> -- Frank DeLuccia wrote: --
> Hello All,
> I'm trying to export XML using osql or the sp_makewebtask and
having
> problems having IE 6.0 read the outputted format. The file is
outputted
> contains control line feeds in the middle of a row causing IE to
throw up
> errors. Is there a way to avoid these random control linefeeds in
the file?
> Any help is greatly appreciated.
> Thanks in advance,
> Frank
>
>
|||Sorry, this was not a bug, this was a design decision that you needed to
recompose the FOR XML results yourself over ODBC.
Yukon will do it for you.
Best regards
Michael
"mizwhite" <anonymous@.discussions.microsoft.com> wrote in message
news:DAEB7668-1A03-4156-A99D-531700166BC1@.microsoft.com...
> Hi Frank,
> We have experienced this problem also and found out that there is actually
> a bug with FOR...XML statements when executed over ODBC. As you've
> discovered, it only returns 2083 characters per line.
> Here is the link to the KB article:
> http://support.microsoft.com/default...;en-us;Q275583
> If you do a "Search By Author" on my username, you can find a list of some
> other posts I've made regarding this issue one of which discusses a
> workaround using OLE DB and DTS.
> HTH,
> Denise E. White
> Technical Director
> The Next Version Ltd. UK
> www.thenextversion.com
> www.denisewhite.co.uk
>
> -- Frank DeLuccia wrote: --
> Hello All,
> I'm trying to export XML using osql or the sp_makewebtask and
> having
> problems having IE 6.0 read the outputted format. The file is
> outputted
> contains control line feeds in the middle of a row causing IE to throw
> up
> errors. Is there a way to avoid these random control linefeeds in the
> file?
> Any help is greatly appreciated.
> Thanks in advance,
> Frank
>
>

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

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
>

Exporting XML data as a table in SQL Server Express

Hi there!

This is a part of the XML file that I have:

<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>

- <NewDataSet>

- <xschema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true">

- <xs:complexType>

- <xs:choice maxOccurs="unbounded">

- <xs:element name="HdrStateProv">

- <xs:complexType>

- <xsequence>

<xs:element name="stateProvID" type="xs:int" minOccurs="0" />

<xs:element name="stateProvNme" type="xstring" minOccurs="0" />

<xs:element name="CountryID" type="xs:int" minOccurs="0" />

</xsequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xschema>

- <HdrStateProv>

<stateProvID>34</stateProvID>

<stateProvNme>Alabama</stateProvNme>

<CountryID>225</CountryID>

</HdrStateProv>

As you can see it just has all the 50 states and I have a datatable called HdrStateProv with three fields, stateProvID, stateProvNme, CountryID in my SQL Server Express. How would I import(map) this data there? I tried the sqlbulkimport KB article but does that not seem to work. Thanks for your time!

Do you mean that sqlbulkimport is non-functional or that it could not import this XML format?

I believe that the bcp utility can import XML, search through Books Online for 'bcp utility' and that should get you started.

Mike

exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks
Here's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks

exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
ThanksHere's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
" SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks

Exporting to CSV format : possible bug ?

Hi,

I have a report, I can export it to Excel, XML and other format ..but I can not export it to .CSV format ...only thing I see in CSV file is some garbage character in first row,first column.

I have RS 2005.

How can I make this thing work ? How can I export report to CSV format ?

thanks,

prashant

fixed..

please see this post ..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=335761&SiteID=1

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,
Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,