Exporting report to WordML

I have been trying to use the XML export along with an XSL file to try and
generate WordML files from reporting services, and so far have had no real
success. I have tried using the XSLT Inference Tool and following the VERY
limited documentation that exists for it, but it just does not want to work
for me.
So, I have 2 questions:
1) Is there any good information/examples of using Word 2003, the XSLT
Inference Tool, and/or Visual Studio to get RS to output WordML files?
2) Why hasn't Microsoft released a WordML renderer for Reporting Services? I
can understand not having one for the old formats since MS is trying to phase
them out, but they have an XML renderer, and they are pushing the WordML
format, so it would seem to make sense to release a WordML renderer...Anyone have any thoughts/Hints/Tips for this?
"jwattsCOL" wrote:
> I have been trying to use the XML export along with an XSL file to try and
> generate WordML files from reporting services, and so far have had no real
> success. I have tried using the XSLT Inference Tool and following the VERY
> limited documentation that exists for it, but it just does not want to work
> for me.
> So, I have 2 questions:
> 1) Is there any good information/examples of using Word 2003, the XSLT
> Inference Tool, and/or Visual Studio to get RS to output WordML files?
> 2) Why hasn't Microsoft released a WordML renderer for Reporting Services? I
> can understand not having one for the old formats since MS is trying to phase
> them out, but they have an XML renderer, and they are pushing the WordML
> format, so it would seem to make sense to release a WordML renderer...
>

Exporting in XML format

Is there a way of exporting data in XML format from SQL Server 2000, there is a wizard for exporting to a html file but I can't seem to find one to export into an XML file?A week and no replies?

All I want to do is create an XML document from sql server 2000 and have it written to a shared drive so I can read it from our webserver!

I know to produce on the fly such as SELECT * FROM VW_Web_Curr_Projs1 FOR XML AUTO, but how to write this result to an xml document
Or am I going about this the wrong way, any help please?|||

Take a look here

http://www.perfectxml.com/articles/XML/ExportSQLXML.asp

sql

EXporting data to 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,just adding for xml clause gives output in xml format
select * from <table> for xml. However this has lot of options too.
BOL has this example
CREATE VIEW p AS
SELECT od.OrderID,
pr.ProductName,
od.Quantity,
od.UnitPrice,
od.Quantity * od.UnitPrice AS total
FROM Products AS pr
JOIN
[Order Details] AS od
ON
pr.ProductID = od.ProductID
And then write the SELECT statement:
SELECT c.CompanyName,
o.OrderID,
o.OrderDate,
p.ProductName,
p.Quantity,
p.UnitPrice,
p.total
FROM Customers AS c
JOIN
Orders AS o
ON
c.CustomerID = o.CustomerID
JOIN
p
ON
o.OrderID = p.OrderID
FOR XML AUTO
--
Regards
R.D
--Knowledge gets doubled when shared
"Enric" wrote:

> 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 customar
y,
> a xml.
> Any advice or though woud be greatly.
> Regards,|||thanks a lot,
"R.D" wrote:
> just adding for xml clause gives output in xml format
> select * from <table> for xml. However this has lot of options too.
> BOL has this example
> CREATE VIEW p AS
> SELECT od.OrderID,
> pr.ProductName,
> od.Quantity,
> od.UnitPrice,
> od.Quantity * od.UnitPrice AS total
> FROM Products AS pr
> JOIN
> [Order Details] AS od
> ON
> pr.ProductID = od.ProductID
> And then write the SELECT statement:
> SELECT c.CompanyName,
> o.OrderID,
> o.OrderDate,
> p.ProductName,
> p.Quantity,
> p.UnitPrice,
> p.total
> FROM Customers AS c
> JOIN
> Orders AS o
> ON
> c.CustomerID = o.CustomerID
> JOIN
> p
> ON
> o.OrderID = p.OrderID
> FOR XML AUTO
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Enric" wrote:
>|||Enric,
Also undestand that the XML that is produced is an XML fragment not an valid
XML document. You'll need to add a XML tag and a root element.
HTH
Jerry
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:5FC7B338-98E4-4C5A-A62E-A81E661DD1AF@.microsoft.com...
> just adding for xml clause gives output in xml format
> select * from <table> for xml. However this has lot of options too.
> BOL has this example
> CREATE VIEW p AS
> SELECT od.OrderID,
> pr.ProductName,
> od.Quantity,
> od.UnitPrice,
> od.Quantity * od.UnitPrice AS total
> FROM Products AS pr
> JOIN
> [Order Details] AS od
> ON
> pr.ProductID = od.ProductID
> And then write the SELECT statement:
> SELECT c.CompanyName,
> o.OrderID,
> o.OrderDate,
> p.ProductName,
> p.Quantity,
> p.UnitPrice,
> p.total
> FROM Customers AS c
> JOIN
> Orders AS o
> ON
> c.CustomerID = o.CustomerID
> JOIN
> p
> ON
> o.OrderID = p.OrderID
> FOR XML AUTO
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Enric" wrote:
>

Exporting data from Excel to SQL DATABASE

Hi,
Is it possible to export data from Excel spreadsheet to different tables in
SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
need to do this every time the user , say clicks a button on the Excel sheet
.
Thanks for any help.
--
pmudYou probably want to ask this in the Office/Excel newsgroup regarding
specific functionality in Excel. You can obviously export the XML and then
using any of the XML import functionality of SQL Server 2000 such as the
SQLXML XML Bulkload object or OpenXML in T-SQL...
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
> Hi,
> Is it possible to export data from Excel spreadsheet to different tables
> in
> SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
> need to do this every time the user , say clicks a button on the Excel
> sheet.
> Thanks for any help.
> --
> pmud|||Hi Michael,
As you said that I can export XML & then using XML import functionality fo
sql server 2000 , I can export data from excel to SQL database. But do I hav
e
to do this process everytime a spreadsheet comes in ? or this can be coded
somewhere so that the process is automatically taken care of?
Is there any walkthrough or aticle which I can read.. Thanks..
"Michael Rys [MSFT]" wrote:

> You probably want to ask this in the Office/Excel newsgroup regarding
> specific functionality in Excel. You can obviously export the XML and then
> using any of the XML import functionality of SQL Server 2000 such as the
> SQLXML XML Bulkload object or OpenXML in T-SQL...
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
>
>|||I would assume that you could use scripting to do it, but I am not a
scripting expert and I think checking with the Excel experts would be a good
idea.
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...
> Hi Michael,
> As you said that I can export XML & then using XML import functionality fo
> sql server 2000 , I can export data from excel to SQL database. But do I
> have
> to do this process everytime a spreadsheet comes in ? or this can be coded
> somewhere so that the process is automatically taken care of?
> Is there any walkthrough or aticle which I can read.. Thanks..
> "Michael Rys [MSFT]" wrote:
>|||Hi Michael,
Leaving the scripting apart, how can I export data from excel to XML only'
Is there some walkthrough or article that shows this?
Thanks a lot.
"Michael Rys [MSFT]" wrote:

> I would assume that you could use scripting to do it, but I am not a
> scripting expert and I think checking with the Excel experts would be a go
od
> idea.
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...
>
>|||See under save as... There should be some XML options (at least in Excel
2003).
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:947DAF2A-D2EB-4D7A-989E-E4EBABF0ED33@.microsoft.com...
> Hi Michael,
> Leaving the scripting apart, how can I export data from excel to XML
> only'
> Is there some walkthrough or article that shows this?
> Thanks a lot.
> "Michael Rys [MSFT]" wrote:
>

Exporting data from Excel to SQL DATABASE

Hi,
Is it possible to export data from Excel spreadsheet to different tables in
SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
need to do this every time the user , say clicks a button on the Excel sheet.
Thanks for any help.
pmud
You probably want to ask this in the Office/Excel newsgroup regarding
specific functionality in Excel. You can obviously export the XML and then
using any of the XML import functionality of SQL Server 2000 such as the
SQLXML XML Bulkload object or OpenXML in T-SQL...
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
> Hi,
> Is it possible to export data from Excel spreadsheet to different tables
> in
> SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
> need to do this every time the user , say clicks a button on the Excel
> sheet.
> Thanks for any help.
> --
> pmud
|||Hi Michael,
As you said that I can export XML & then using XML import functionality fo
sql server 2000 , I can export data from excel to SQL database. But do I have
to do this process everytime a spreadsheet comes in ? or this can be coded
somewhere so that the process is automatically taken care of?
Is there any walkthrough or aticle which I can read.. Thanks..
"Michael Rys [MSFT]" wrote:

> You probably want to ask this in the Office/Excel newsgroup regarding
> specific functionality in Excel. You can obviously export the XML and then
> using any of the XML import functionality of SQL Server 2000 such as the
> SQLXML XML Bulkload object or OpenXML in T-SQL...
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
>
>
|||I would assume that you could use scripting to do it, but I am not a
scripting expert and I think checking with the Excel experts would be a good
idea.
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> As you said that I can export XML & then using XML import functionality fo
> sql server 2000 , I can export data from excel to SQL database. But do I
> have
> to do this process everytime a spreadsheet comes in ? or this can be coded
> somewhere so that the process is automatically taken care of?
> Is there any walkthrough or aticle which I can read.. Thanks..
> "Michael Rys [MSFT]" wrote:
|||Hi Michael,
Leaving the scripting apart, how can I export data from excel to XML only?
Is there some walkthrough or article that shows this?
Thanks a lot.
"Michael Rys [MSFT]" wrote:

> I would assume that you could use scripting to do it, but I am not a
> scripting expert and I think checking with the Excel experts would be a good
> idea.
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...
>
>
|||See under save as... There should be some XML options (at least in Excel
2003).
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:947DAF2A-D2EB-4D7A-989E-E4EBABF0ED33@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Leaving the scripting apart, how can I export data from excel to XML
> only?
> Is there some walkthrough or article that shows this?
> Thanks a lot.
> "Michael Rys [MSFT]" wrote:

Exporting data as XML file

To get the data from the database table as an XML file we will use " for xml" clause in sql statement in SQLSERVER.

But i am not able to generate the following things in that XML file.

please let me know how to get the following details in XML file using sql statement?

<?xml version="1.0" encoding="UTF-8" ?>

- <listOfIDXRefData xmlns=some url here>

Try the T-SQL forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

-Jamie

Exportin table in xml file

Hello everyone,
I need to save data into an xml file. Now I do this with Visual Basic using
the ado-recordset method 'Save'. I'd like to do this task with a sql server
stored procedure.
Does anyone know if it is possible, and how?
--
Thank you everyoone,
Walker BohYou could do something like
EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
' -od:\FTPresentation\imagefmt.fmt'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> Hello everyone,
> I need to save data into an xml file. Now I do this with Visual Basic
using
> the ado-recordset method 'Save'. I'd like to do this task with a sql
server
> stored procedure.
> Does anyone know if it is possible, and how?
> --
> Thank you everyoone,
> Walker Boh|||It doesn't work as I need. It doesn't create an xml file that I can open wit
h
IE for example, nor it has tha same structure the recordset methis save
create.
thank you anyway
"Wayne Snyder" wrote:

> You could do something like
> EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
> ' -od:\FTPresentation\imagefmt.fmt'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
> news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> using
> server
>
>

Exportin table in xml file

Hello everyone,
I need to save data into an xml file. Now I do this with Visual Basic using
the ado-recordset method 'Save'. I'd like to do this task with a sql server
stored procedure.
Does anyone know if it is possible, and how?
Thank you everyoone,
Walker Boh
You could do something like
EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
' -od:\FTPresentation\imagefmt.fmt'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> Hello everyone,
> I need to save data into an xml file. Now I do this with Visual Basic
using
> the ado-recordset method 'Save'. I'd like to do this task with a sql
server
> stored procedure.
> Does anyone know if it is possible, and how?
> --
> Thank you everyoone,
> Walker Boh
|||It doesn't work as I need. It doesn't create an xml file that I can open with
IE for example, nor it has tha same structure the recordset methis save
create.
thank you anyway
"Wayne Snyder" wrote:

> You could do something like
> EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
> ' -od:\FTPresentation\imagefmt.fmt'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
> news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> using
> server
>
>

Exportin table in xml file

Hello everyone,
I need to save data into an xml file. Now I do this with Visual Basic using
the ado-recordset method 'Save'. I'd like to do this task with a sql server
stored procedure.
Does anyone know if it is possible, and how?
--
Thank you everyoone,
Walker BohYou could do something like
EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
' -od:\FTPresentation\imagefmt.fmt'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> Hello everyone,
> I need to save data into an xml file. Now I do this with Visual Basic
using
> the ado-recordset method 'Save'. I'd like to do this task with a sql
server
> stored procedure.
> Does anyone know if it is possible, and how?
> --
> Thank you everyoone,
> Walker Boh|||It doesn't work as I need. It doesn't create an xml file that I can open with
IE for example, nor it has tha same structure the recordset methis save
create.
thank you anyway
"Wayne Snyder" wrote:
> You could do something like
> EXEC master..xp_cmdshell "OSQL -E -S -Q"Select * from table for xml
> ' -od:\FTPresentation\imagefmt.fmt'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Walker Boh" <WalkerBoh@.discussions.microsoft.com> wrote in message
> news:F1F0C715-25E3-4337-99E6-4CF4F0DDEF49@.microsoft.com...
> > Hello everyone,
> >
> > I need to save data into an xml file. Now I do this with Visual Basic
> using
> > the ado-recordset method 'Save'. I'd like to do this task with a sql
> server
> > stored procedure.
> > Does anyone know if it is possible, and how?
> > --
> > Thank you everyoone,
> > Walker Boh
>
>

Export/Import tables from MSQL from/to .xml files

When I try to export a sql table to a .xml file I get the following error
when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
sqloledb must be specified as the data provider.
However, sqloledb does not appear in the list of ole db providers. Does
anyone know how I can register sqloledb as a data provider please
I also have a problem using sql query analyzer to access a .xml file. All of
the examples in the help files show encoded xml data rather than accessing a
.xml data file on disk. Does anyone know how to link in the .xml file to sq
l
script please.
Regards,
Alan de Wetsqloledb should already be part of the OS. If you install MDAC / SqlClient
Tools / VS, SQLOLEDB should be installed.
Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
verification. If the dll is there and is registered, then SQLOLEDB should be
there.
To use SQLOLEDB as the provider in the connection string, append
Provider=SQLOLEDB to the connection string.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
> When I try to export a sql table to a .xml file I get the following error
> when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
> sqloledb must be specified as the data provider.
> However, sqloledb does not appear in the list of ole db providers. Does
> anyone know how I can register sqloledb as a data provider please
> I also have a problem using sql query analyzer to access a .xml file. All
> of
> the examples in the help files show encoded xml data rather than accessing
> a
> .xml data file on disk. Does anyone know how to link in the .xml file to
> sql
> script please.
> Regards,
> Alan de Wet|||Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole d
b
providers when I try to do an export from the Import and Export tool in MSQL
and am entering the Destination details. I am not trying to do the export
from a VB application so I do not have a connection string. SQLOLEDB is in
C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). Ho
w
do I check if it is registered please?
Regards,
Alan
"Chandra Kalyanaraman [MSFT]" wrote:

> sqloledb should already be part of the OS. If you install MDAC / SqlClient
> Tools / VS, SQLOLEDB should be installed.
> Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
> verification. If the dll is there and is registered, then SQLOLEDB should
be
> there.
> To use SQLOLEDB as the provider in the connection string, append
> Provider=SQLOLEDB to the connection string.
>
> thanks
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
> news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
>
>|||Is it true that SQLXMLOLEDB provider supports data export via DTS
Import/Export Wizard?
Best regards
Martin Rakhmanov
"Alan de Wet" wrote:
> Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole
db
> providers when I try to do an export from the Import and Export tool in MS
QL
> and am entering the Destination details. I am not trying to do the export
> from a VB application so I do not have a connection string. SQLOLEDB is in
> C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb).
How
> do I check if it is registered please?
> Regards,
> Alan
> "Chandra Kalyanaraman [MSFT]" wrote:
>|||After I installed sqlxml 2 new Data Providers (SQLXMLOLEDB and
SQLXMLOLEDB.3.0) appear in the list when I select my destination. I have
tried both of them. When I select either one I get a message saying: To
connect click properties and enter the required information. When I click on
properties I do not see sqloledb as a ole db provider but if I select any
other provider, click OK and then click next I get a message saying that
SQLOLEDB must be specified as the data provider. If I select either
sqxmloledb or sqlxmloledb.3.0 as the ole db provider under properties I am
then asked to enter Data Source, Location and Server Log On Information whic
h
does not make sense as I am trying to export to a .xml file.
So to answer your question, maybe it was supposed to but it does not appear
to support data export via DTS Import/Export Wizard.
Best Regards,
Alan de Wet
"jimmers" wrote:
> Is it true that SQLXMLOLEDB provider supports data export via DTS
> Import/Export Wizard?
> Best regards
> Martin Rakhmanov
>
> "Alan de Wet" wrote:
>

Export/Import tables from MSQL from/to .xml files

When I try to export a sql table to a .xml file I get the following error
when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
sqloledb must be specified as the data provider.
However, sqloledb does not appear in the list of ole db providers. Does
anyone know how I can register sqloledb as a data provider please
I also have a problem using sql query analyzer to access a .xml file. All of
the examples in the help files show encoded xml data rather than accessing a
..xml data file on disk. Does anyone know how to link in the .xml file to sql
script please.
Regards,
Alan de Wet
sqloledb should already be part of the OS. If you install MDAC / SqlClient
Tools / VS, SQLOLEDB should be installed.
Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
verification. If the dll is there and is registered, then SQLOLEDB should be
there.
To use SQLOLEDB as the provider in the connection string, append
Provider=SQLOLEDB to the connection string.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
> When I try to export a sql table to a .xml file I get the following error
> when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
> sqloledb must be specified as the data provider.
> However, sqloledb does not appear in the list of ole db providers. Does
> anyone know how I can register sqloledb as a data provider please
> I also have a problem using sql query analyzer to access a .xml file. All
> of
> the examples in the help files show encoded xml data rather than accessing
> a
> .xml data file on disk. Does anyone know how to link in the .xml file to
> sql
> script please.
> Regards,
> Alan de Wet
|||Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole db
providers when I try to do an export from the Import and Export tool in MSQL
and am entering the Destination details. I am not trying to do the export
from a VB application so I do not have a connection string. SQLOLEDB is in
C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). How
do I check if it is registered please?
Regards,
Alan
"Chandra Kalyanaraman [MSFT]" wrote:

> sqloledb should already be part of the OS. If you install MDAC / SqlClient
> Tools / VS, SQLOLEDB should be installed.
> Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
> verification. If the dll is there and is registered, then SQLOLEDB should be
> there.
> To use SQLOLEDB as the provider in the connection string, append
> Provider=SQLOLEDB to the connection string.
>
> thanks
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
> news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
>
>
|||Is it true that SQLXMLOLEDB provider supports data export via DTS
Import/Export Wizard?
Best regards
Martin Rakhmanov
"Alan de Wet" wrote:
[vbcol=seagreen]
> Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole db
> providers when I try to do an export from the Import and Export tool in MSQL
> and am entering the Destination details. I am not trying to do the export
> from a VB application so I do not have a connection string. SQLOLEDB is in
> C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). How
> do I check if it is registered please?
> Regards,
> Alan
> "Chandra Kalyanaraman [MSFT]" wrote:
|||After I installed sqlxml 2 new Data Providers (SQLXMLOLEDB and
SQLXMLOLEDB.3.0) appear in the list when I select my destination. I have
tried both of them. When I select either one I get a message saying: To
connect click properties and enter the required information. When I click on
properties I do not see sqloledb as a ole db provider but if I select any
other provider, click OK and then click next I get a message saying that
SQLOLEDB must be specified as the data provider. If I select either
sqxmloledb or sqlxmloledb.3.0 as the ole db provider under properties I am
then asked to enter Data Source, Location and Server Log On Information which
does not make sense as I am trying to export to a .xml file.
So to answer your question, maybe it was supposed to but it does not appear
to support data export via DTS Import/Export Wizard.
Best Regards,
Alan de Wet
"jimmers" wrote:
[vbcol=seagreen]
> Is it true that SQLXMLOLEDB provider supports data export via DTS
> Import/Export Wizard?
> Best regards
> Martin Rakhmanov
>
> "Alan de Wet" wrote:

Export/Import data with XML - BLOB's, how to?

I need export data from few master->detail tables and then import this data
into other database with the same structure.
I.e. export some my "object" and import.
The question is: how can I export and import tables which have ntext, image
columns?
Thank you.
Did you look at FOR XML SELECT statements for exporting and either the
client-side SQLXML Bulkload object or the server-side OpenXML rowset
provider for importing?
Best regards
Michael
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:%235nORZ5NFHA.2604@.TK2MSFTNGP10.phx.gbl...
>I need export data from few master->detail tables and then import this data
>into other database with the same structure.
> I.e. export some my "object" and import.
> The question is: how can I export and import tables which have ntext,
> image columns?
> Thank you.
>

Export/Import data with XML - BLOB's, how to?

I need export data from few master->detail tables and then import this data
into other database with the same structure.
I.e. export some my "object" and import.
The question is: how can I export and import tables which have ntext, image
columns?
Thank you.Did you look at FOR XML SELECT statements for exporting and either the
client-side SQLXML Bulkload object or the server-side OpenXML rowset
provider for importing?
Best regards
Michael
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:%235nORZ5NFHA.2604@.TK2MSFTNGP10.phx.gbl...
>I need export data from few master->detail tables and then import this data
>into other database with the same structure.
> I.e. export some my "object" and import.
> The question is: how can I export and import tables which have ntext,
> image columns?
> Thank you.
>

Export XML to SQL 2K database

hello everyone-
I am fairly new to XML and understand SQL to XML transactions. However, I
am now faced with a mindnumbing problem. I've read and researched endlessly
,
but can find nothing succint- no "here's where to start..." My company is
receiving NewsML feeds from which I need to extract particular data
(headline, dateline, body, etc.) for storage in a SQL table. Could someone
graciously point me in the right direction as to how I would get going on
this? There seem to be so many ways to do this and I've seen several msdn k
b
articles, but I am still so . This will end up in an ASP.NET
application, using VB.NET -C# if I have to. :o)
TIA- JennaHello Jenna!
I'd pass the feed text as a parameter to a stored procedure and use openxml
to extract the data.
http://msdn.microsoft.com/library/d...br />
5c89.asp
DECLARE @.NewsML varchar(8000)
-- NewsML sample xml from <a href="http://links.10026.com/?link=http://about.reuters.com/newsml/newsmlsam
ple.asp">Reuters</a>
SET @.NewsML = '<NewsML Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSML"
>
<Catalog href="http://links.10026.com/?link=http://www.reuters.com/newsml/mastercatalog.xml"/>
<NewsEnvelope>
<DateAndTime>20020703T150148+0000</DateAndTime>
<NewsService FormalName="RTR_TNS"/>
<NewsProduct FormalName="TXT"/>
<Priority FormalName="3"/>
</NewsEnvelope>
<NewsItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSITEM">
<Identification>
<NewsIdentifier>
<ProviderId>reuters.com</ProviderId>
<DateId>20020703</DateId>
<NewsItemId>MTFH19813_2002-07-03_15-01-48_LOSAPJZA1</NewsItemId>
<RevisionId Update="N" PreviousRevision="0">1</RevisionId>
<PublicIdentifier>urn:newsml:reuters.com:20020703:MTFH19813_2002-07-03_15-01
-48_LOSAPJZA1:1</PublicIdentifier>
</NewsIdentifier>
<DateLabel>2002-07-03 15:01:48 GMT (Reuters)</DateLabel>
</Identification>
<NewsManagement>
<NewsItemType FormalName="News"/>
<FirstCreated>20020703T150148+0000</FirstCreated>
<ThisRevisionCreated>20020703T150148+0000</ThisRevisionCreated>
<Status FormalName="Usable"/>
<Urgency FormalName="3"/>
</NewsManagement>
<NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-07-0
3_15-01-48_LOSAPJZA1_MAIN_NC" xml:lang="en">
<TopicSet FormalName="HighImportance">
<Topic Duid="ts_1">
<TopicType FormalName="CategoryCode"/>
<FormalName Scheme="MediaCategory">OEC</FormalName>
<Description xml:lang="en">Economic news, EC, business/financial pages</Desc
ription>
<Property FormalName="WhyPresent" Value="Classifier"/>
</Topic>
</TopicSet>
<Role FormalName="Main"/>
<AdministrativeMetadata>
<FileName>2002-07- 03T150148Z_01_LOSAPJZA1_RTRIDST_0_MARKET
S-EURO-RATES.XML</
FileName>
<Provider>
<Party FormalName="Reuters"/>
</Provider>
<Source>
<Party FormalName="Reuters"/>
</Source>
<Property FormalName="SourceFeed" Value="IDS"/>
<Property FormalName="IDSPublisher" Value="http://www.reuters.com/ids"/>
</AdministrativeMetadata>
<!--Single "Main Text" inner NewsComponent-->
<NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-07-0
3_15-01-48_LOSAPJZA1_MAIN_TEXT_NC" xml:lang="en">
<Role FormalName="Main Text"/>
<NewsLines>
<HeadLine>Euro Rates at 1500 GMT - July 3</HeadLine>
<ByLine/>
<DateLine>July 3, 2002</DateLine>
<CreditLine>REUTERS</CreditLine>
<CopyrightLine>? Reuters 2002. All rights reserved. Republication or redist
ribution of Reuters content, including by caching, framing or similar means,
is expressly prohibited without the prior written consent of Reuters. Reute
rs and the Reuters sphere logo are registered trademarks and trademarks of t
he Reuters group of companies around the world.</CopyrightLine>
<SlugLine>MARKETS-EURO-RATES</SlugLine>
<NewsLine>
<NewsLineType FormalName="Caption"/>
<NewsLineText>MARKETS-EURO-RATES:Euro Rates at 1500 GMT - July 3</NewsLineTe
xt>
</NewsLine>
</NewsLines>
<DescriptiveMetadata>
<Language FormalName="en"/>
<Genre FormalName="Table"/>
<OfInterestTo FormalName="AFA"/>
<OfInterestTo FormalName="CSA"/>
<OfInterestTo FormalName="LBY"/>
<OfInterestTo FormalName="RWSA"/>
<OfInterestTo FormalName="RWS"/>
<OfInterestTo FormalName="REULB"/>
<OfInterestTo FormalName="GNS"/>
<OfInterestTo FormalName="SXNA"/>
<TopicOccurrence Importance="High" Topic="#ts_1"/>
</DescriptiveMetadata>
<ContentItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN1_TEXT_CI">
<MediaType FormalName="Text"/>
<Format FormalName="XHTML"/>
<Characteristics>
<Property FormalName="ContentID" Value="urn:newsml:reuters.com:20020703:MTFH
19813_2002-07-03_15-01-48_LOSAPJZA1_TXT:1"/>
<Property FormalName="ContentCreationDateAndTime" Value="20020703T150148+000
0"/>
<Property FormalName="USN" Value="LOSAPJZA1"/>
<Property FormalName="Creator" Value="RTR_JANUS 2.300"/>
</Characteristics>
<DataContent>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title/>
</head>
<body>
<p> LONDON, July 3 (Reuters) - Following are the middle exchange rates for
leading currencies against the euro:</p>
<p> CURRENT HIGH LOW US Dollar
0.9803 0.9863 0.9767 Japanese Yen 117.48 118.23
117.29 British Pound 0.6423 0.6436 0.6408 Swiss Fran
c 1.4627 1.4664 1.461 Danish Crown
7.428 7.4292 7.428 Gr Drachma - -
- Norwegian Crown 7.3362 7.3745 7.3306 Swedish Crow
n 9.102 9.134 9.058 Australian Dollar 1.7542
1.7591 1.7434 Canadian Dollar 1.5034 1.5046 1.4921
Hong Kong Dollar 7.6464 7.6779 7.6224 Russian Rouble
30.92 31.1 30.81 Singapore Dollar 1.7335 1.7
418 1.731 </p>
</body>
</html>
</DataContent>
</ContentItem>
</NewsComponent>
</NewsComponent>
</NewsItem>
</NewsML>'
DECLARE @.NewsMLPtr int
EXEC sp_xml_preparedocument @.NewsMLPtr OUTPUT, @.NewsML
SELECT *
FROM OPENXML(@.NewsMLPtr, '/NewsML/NewsItem')
WITH (
Duid varchar(100) '@.Duid',
RevisionId tinyint 'Identification/NewsIdentifier/RevisionId',
FirstCreated varchar(100) 'NewsManagement/FirstCreated',
Topic varchar(100) 'NewsComponent/TopicSet/Topic/Description'
)
EXEC sp_xml_removedocument @.NewsMLPtr|||Samu thanks so much. I'm going to give this a shot!
"Samu Lang" wrote:

> Hello Jenna!
> I'd pass the feed text as a parameter to a stored procedure and use openxm
l to extract the data.
> http://msdn.microsoft.com/library/d... />
z_5c89.asp
>
> DECLARE @.NewsML varchar(8000)
>
>
> -- NewsML sample xml from <a href="http://links.10026.com/?link=http://about.reuters.com/newsml/newsmls
ample.asp">Reuters</a>
> SET @.NewsML = '<NewsML Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSM
L">
> <Catalog href="http://links.10026.com/?link=http://www.reuters.com/newsml/mastercatalog.xml"/>
> <NewsEnvelope>
> <DateAndTime>20020703T150148+0000</DateAndTime>
> <NewsService FormalName="RTR_TNS"/>
> <NewsProduct FormalName="TXT"/>
> <Priority FormalName="3"/>
> </NewsEnvelope>
> <NewsItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSITEM">
> <Identification>
> <NewsIdentifier>
> <ProviderId>reuters.com</ProviderId>
> <DateId>20020703</DateId>
> <NewsItemId>MTFH19813_2002-07-03_15-01-48_LOSAPJZA1</NewsItemId>
> <RevisionId Update="N" PreviousRevision="0">1</RevisionId>
> <PublicIdentifier>urn:newsml:reuters.com:20020703:MTFH19813_2002-07-03
_15-01-48_LOSAPJZA1:1</PublicIdentifier>
> </NewsIdentifier>
> <DateLabel>2002-07-03 15:01:48 GMT (Reuters)</DateLabel>
> </Identification>
> <NewsManagement>
> <NewsItemType FormalName="News"/>
> <FirstCreated>20020703T150148+0000</FirstCreated>
> <ThisRevisionCreated>20020703T150148+0000</ThisRevisionCreated>
> <Status FormalName="Usable"/>
> <Urgency FormalName="3"/>
> </NewsManagement>
> <NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-
07-03_15-01-48_LOSAPJZA1_MAIN_NC" xml:lang="en">
> <TopicSet FormalName="HighImportance">
> <Topic Duid="ts_1">
> <TopicType FormalName="CategoryCode"/>
> <FormalName Scheme="MediaCategory">OEC</FormalName>
> <Description xml:lang="en">Economic news, EC, business/financial page
s</Description>
> <Property FormalName="WhyPresent" Value="Classifier"/>
> </Topic>
> </TopicSet>
> <Role FormalName="Main"/>
> <AdministrativeMetadata>
> <FileName>2002-07- 03T150148Z_01_LOSAPJZA1_RTRIDST_0_MARKET
S-EURO-RATES
.XML</FileName>
> <Provider>
> <Party FormalName="Reuters"/>
> </Provider>
> <Source>
> <Party FormalName="Reuters"/>
> </Source>
> <Property FormalName="SourceFeed" Value="IDS"/>
> <Property FormalName="IDSPublisher" Value="http://www.reuters.com/ids"
/>
> </AdministrativeMetadata>
> <!--Single "Main Text" inner NewsComponent-->
> <NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002
-07-03_15-01-48_LOSAPJZA1_MAIN_TEXT_NC" xml:lang="en">
> <Role FormalName="Main Text"/>
> <NewsLines>
> <HeadLine>Euro Rates at 1500 GMT - July 3</HeadLine>
> <ByLine/>
> <DateLine>July 3, 2002</DateLine>
> <CreditLine>REUTERS</CreditLine>
> <CopyrightLine>? Reuters 2002. All rights reserved. Republication or redistr
ibution of Reuters content, including by caching, framing or similar means, is expre
ssly prohibited without the prior written consent of Reuters. Reuters and the Reuter
s s
phere logo are registered trademarks and trademarks of the Reuters group of companies aroun
d the world.</CopyrightLine>
> <SlugLine>MARKETS-EURO-RATES</SlugLine>
> <NewsLine>
> <NewsLineType FormalName="Caption"/>
> <NewsLineText>MARKETS-EURO-RATES:Euro Rates at 1500 GMT - July 3</Ne
wsLineText>
> </NewsLine>
> </NewsLines>
> <DescriptiveMetadata>
> <Language FormalName="en"/>
> <Genre FormalName="Table"/>
> <OfInterestTo FormalName="AFA"/>
> <OfInterestTo FormalName="CSA"/>
> <OfInterestTo FormalName="LBY"/>
> <OfInterestTo FormalName="RWSA"/>
> <OfInterestTo FormalName="RWS"/>
> <OfInterestTo FormalName="REULB"/>
> <OfInterestTo FormalName="GNS"/>
> <OfInterestTo FormalName="SXNA"/>
> <TopicOccurrence Importance="High" Topic="#ts_1"/>
> </DescriptiveMetadata>
> <ContentItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN1_TEXT_
CI">
> <MediaType FormalName="Text"/>
> <Format FormalName="XHTML"/>
> <Characteristics>
> <Property FormalName="ContentID" Value="urn:newsml:reuters.com:20020
703:MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_TXT:1"/>
> <Property FormalName="ContentCreationDateAndTime" Value="20020703T15
0148+0000"/>
> <Property FormalName="USN" Value="LOSAPJZA1"/>
> <Property FormalName="Creator" Value="RTR_JANUS 2.300"/>
> </Characteristics>
> <DataContent>
> <html xmlns="http://www.w3.org/1999/xhtml">
> <head>
> <title/>
> </head>
> <body>
> <p> LONDON, July 3 (Reuters) - Following are the middle exchange
rates for leading currencies against the euro:</p>
> <p> CURRENT HIGH LOW US Dollar
0.9803 0.9863 0.9767 Japanese Yen 117.48 118.2
3 117.29 British Pound 0.6423 0.6436 0.6408 Swiss Franc[/c
olor]
1.4627 1.4664 1.461 Danish Crown 7.428 7.4292
7.428 Gr Drachma - - - Norwegian
Crown 7.3362 7.3745 7.3306 Swedish Crown
9.102 9.13
4 9.058 Australian Dollar 1.7542 1.7591 1.7434 Canadi
an Dollar 1.5034 1.5046 1.4921 Hong Kong Dollar
7.6464 7.6779 7.6224 Russian Rouble 30.92 31.
1 30.81 Singapore Doll
ar 1.7335 1.7418 1.731 </p>
> </body>
> </html>
> </DataContent>
> </ContentItem>
> </NewsComponent>
> </NewsComponent>
> </NewsItem>
> </NewsML>'
>
>
> DECLARE @.NewsMLPtr int
> EXEC sp_xml_preparedocument @.NewsMLPtr OUTPUT, @.NewsML
> SELECT *
> FROM OPENXML(@.NewsMLPtr, '/NewsML/NewsItem')
> WITH (
> Duid varchar(100) '@.Duid',
> RevisionId tinyint 'Identification/NewsIdentifier/RevisionId',
> FirstCreated varchar(100) 'NewsManagement/FirstCreated',
> Topic varchar(100) 'NewsComponent/TopicSet/Topic/Description'
> )
> EXEC sp_xml_removedocument @.NewsMLPtr
>

Export XML to SQL 2K database

hello everyone-
I am fairly new to XML and understand SQL to XML transactions. However, I
am now faced with a mindnumbing problem. I've read and researched endlessly,
but can find nothing succint- no "here's where to start..." My company is
receiving NewsML feeds from which I need to extract particular data
(headline, dateline, body, etc.) for storage in a SQL table. Could someone
graciously point me in the right direction as to how I would get going on
this? There seem to be so many ways to do this and I've seen several msdn kb
articles, but I am still so confused. This will end up in an ASP.NET
application, using VB.NET -C# if I have to. :o)
TIA- Jenna
Hello Jenna!
I'd pass the feed text as a parameter to a stored procedure and use openxml to extract the data.
http://msdn.microsoft.com/library/de...oa-oz_5c89.asp
DECLARE @.NewsML varchar(8000)
-- NewsML sample xml from <a href="http://links.10026.com/?link=http://about.reuters.com/newsml/newsmlsample.asp">Reuters</a>
SET @.NewsML = '<NewsML Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSML">
<Catalog href="http://links.10026.com/?link=http://www.reuters.com/newsml/mastercatalog.xml"/>
<NewsEnvelope>
<DateAndTime>20020703T150148+0000</DateAndTime>
<NewsService FormalName="RTR_TNS"/>
<NewsProduct FormalName="TXT"/>
<Priority FormalName="3"/>
</NewsEnvelope>
<NewsItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSITEM">
<Identification>
<NewsIdentifier>
<ProviderId>reuters.com</ProviderId>
<DateId>20020703</DateId>
<NewsItemId>MTFH19813_2002-07-03_15-01-48_LOSAPJZA1</NewsItemId>
<RevisionId Update="N" PreviousRevision="0">1</RevisionId>
<PublicIdentifier>urn:newsml:reuters.com:20020703: MTFH19813_2002-07-03_15-01-48_LOSAPJZA1:1</PublicIdentifier>
</NewsIdentifier>
<DateLabel>2002-07-03 15:01:48 GMT (Reuters)</DateLabel>
</Identification>
<NewsManagement>
<NewsItemType FormalName="News"/>
<FirstCreated>20020703T150148+0000</FirstCreated>
<ThisRevisionCreated>20020703T150148+0000</ThisRevisionCreated>
<Status FormalName="Usable"/>
<Urgency FormalName="3"/>
</NewsManagement>
<NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN_NC" xml:lang="en">
<TopicSet FormalName="HighImportance">
<Topic Duid="ts_1">
<TopicType FormalName="CategoryCode"/>
<FormalName Scheme="MediaCategory">OEC</FormalName>
<Description xml:lang="en">Economic news, EC, business/financial pages</Description>
<Property FormalName="WhyPresent" Value="Classifier"/>
</Topic>
</TopicSet>
<Role FormalName="Main"/>
<AdministrativeMetadata>
<FileName>2002-07-03T150148Z_01_LOSAPJZA1_RTRIDST_0_MARKETS-EURO-RATES.XML</FileName>
<Provider>
<Party FormalName="Reuters"/>
</Provider>
<Source>
<Party FormalName="Reuters"/>
</Source>
<Property FormalName="SourceFeed" Value="IDS"/>
<Property FormalName="IDSPublisher" Value="http://www.reuters.com/ids"/>
</AdministrativeMetadata>
<!--Single "Main Text" inner NewsComponent-->
<NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN_TEXT_NC" xml:lang="en">
<Role FormalName="Main Text"/>
<NewsLines>
<HeadLine>Euro Rates at 1500 GMT - July 3</HeadLine>
<ByLine/>
<DateLine>July 3, 2002</DateLine>
<CreditLine>REUTERS</CreditLine>
<CopyrightLine>? Reuters 2002. All rights reserved. Republication or redistribution of Reuters content, including by caching, framing or similar means, is expressly prohibited without the prior written consent of Reuters. Reuters and the Reuters sphere logo are registered trademarks and trademarks of the Reuters group of companies around the world.</CopyrightLine>
<SlugLine>MARKETS-EURO-RATES</SlugLine>
<NewsLine>
<NewsLineType FormalName="Caption"/>
<NewsLineText>MARKETS-EURO-RATES:Euro Rates at 1500 GMT - July 3</NewsLineText>
</NewsLine>
</NewsLines>
<DescriptiveMetadata>
<Language FormalName="en"/>
<Genre FormalName="Table"/>
<OfInterestTo FormalName="AFA"/>
<OfInterestTo FormalName="CSA"/>
<OfInterestTo FormalName="LBY"/>
<OfInterestTo FormalName="RWSA"/>
<OfInterestTo FormalName="RWS"/>
<OfInterestTo FormalName="REULB"/>
<OfInterestTo FormalName="GNS"/>
<OfInterestTo FormalName="SXNA"/>
<TopicOccurrence Importance="High" Topic="#ts_1"/>
</DescriptiveMetadata>
<ContentItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN1_TEXT_CI">
<MediaType FormalName="Text"/>
<Format FormalName="XHTML"/>
<Characteristics>
<Property FormalName="ContentID" Value="urn:newsml:reuters.com:20020703:MTFH19813_2 002-07-03_15-01-48_LOSAPJZA1_TXT:1"/>
<Property FormalName="ContentCreationDateAndTime" Value="20020703T150148+0000"/>
<Property FormalName="USN" Value="LOSAPJZA1"/>
<Property FormalName="Creator" Value="RTR_JANUS 2.300"/>
</Characteristics>
<DataContent>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title/>
</head>
<body>
<p> LONDON, July 3 (Reuters) - Following are the middle exchange rates for leading currencies against the euro:</p>
<p> CURRENT HIGH LOW US Dollar 0.9803 0.9863 0.9767 Japanese Yen 117.48 118.23 117.29 British Pound 0.6423 0.6436 0.6408 Swiss Franc 1.4627 1.4664 1.461 Danish Crown 7.428 7.4292 7.428 Greek Drachma - - - Norwegian Crown 7.3362 7.3745 7.3306 Swedish Crown 9.102 9.134 9.058 Australian Dollar 1.7542 1.7591 1.7434 Canadian Dollar 1.5034 1.5046 1.4921 Hong Kong Dollar 7.6464 7.6779 7.6224 Russian Rouble 30.92 31.1 30.81 Singapore Dollar 1.7335 1.7418 1.731 </p>
</body>
</html>
</DataContent>
</ContentItem>
</NewsComponent>
</NewsComponent>
</NewsItem>
</NewsML>'
DECLARE @.NewsMLPtr int
EXEC sp_xml_preparedocument @.NewsMLPtr OUTPUT, @.NewsML
SELECT *
FROM OPENXML(@.NewsMLPtr, '/NewsML/NewsItem')
WITH (
Duid varchar(100) '@.Duid',
RevisionId tinyint 'Identification/NewsIdentifier/RevisionId',
FirstCreated varchar(100) 'NewsManagement/FirstCreated',
Topic varchar(100) 'NewsComponent/TopicSet/Topic/Description'
)
EXEC sp_xml_removedocument @.NewsMLPtr
|||Samu thanks so much. I'm going to give this a shot!
"Samu Lang" wrote:

> Hello Jenna!
> I'd pass the feed text as a parameter to a stored procedure and use openxml to extract the data.
> http://msdn.microsoft.com/library/de...oa-oz_5c89.asp
>
> DECLARE @.NewsML varchar(8000)
>
>
> -- NewsML sample xml from <a href="http://links.10026.com/?link=http://about.reuters.com/newsml/newsmlsample.asp">Reuters</a>
> SET @.NewsML = '<NewsML Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSML">
> <Catalog href="http://links.10026.com/?link=http://www.reuters.com/newsml/mastercatalog.xml"/>
> <NewsEnvelope>
> <DateAndTime>20020703T150148+0000</DateAndTime>
> <NewsService FormalName="RTR_TNS"/>
> <NewsProduct FormalName="TXT"/>
> <Priority FormalName="3"/>
> </NewsEnvelope>
> <NewsItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_NEWSITEM">
> <Identification>
> <NewsIdentifier>
> <ProviderId>reuters.com</ProviderId>
> <DateId>20020703</DateId>
> <NewsItemId>MTFH19813_2002-07-03_15-01-48_LOSAPJZA1</NewsItemId>
> <RevisionId Update="N" PreviousRevision="0">1</RevisionId>
> <PublicIdentifier>urn:newsml:reuters.com:20020703: MTFH19813_2002-07-03_15-01-48_LOSAPJZA1:1</PublicIdentifier>
> </NewsIdentifier>
> <DateLabel>2002-07-03 15:01:48 GMT (Reuters)</DateLabel>
> </Identification>
> <NewsManagement>
> <NewsItemType FormalName="News"/>
> <FirstCreated>20020703T150148+0000</FirstCreated>
> <ThisRevisionCreated>20020703T150148+0000</ThisRevisionCreated>
> <Status FormalName="Usable"/>
> <Urgency FormalName="3"/>
> </NewsManagement>
> <NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN_NC" xml:lang="en">
> <TopicSet FormalName="HighImportance">
> <Topic Duid="ts_1">
> <TopicType FormalName="CategoryCode"/>
> <FormalName Scheme="MediaCategory">OEC</FormalName>
> <Description xml:lang="en">Economic news, EC, business/financial pages</Description>
> <Property FormalName="WhyPresent" Value="Classifier"/>
> </Topic>
> </TopicSet>
> <Role FormalName="Main"/>
> <AdministrativeMetadata>
> <FileName>2002-07-03T150148Z_01_LOSAPJZA1_RTRIDST_0_MARKETS-EURO-RATES.XML</FileName>
> <Provider>
> <Party FormalName="Reuters"/>
> </Provider>
> <Source>
> <Party FormalName="Reuters"/>
> </Source>
> <Property FormalName="SourceFeed" Value="IDS"/>
> <Property FormalName="IDSPublisher" Value="http://www.reuters.com/ids"/>
> </AdministrativeMetadata>
> <!--Single "Main Text" inner NewsComponent-->
> <NewsComponent EquivalentsList="no" Essential="no" Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN_TEXT_NC" xml:lang="en">
> <Role FormalName="Main Text"/>
> <NewsLines>
> <HeadLine>Euro Rates at 1500 GMT - July 3</HeadLine>
> <ByLine/>
> <DateLine>July 3, 2002</DateLine>
> <CreditLine>REUTERS</CreditLine>
> <CopyrightLine>? Reuters 2002. All rights reserved. Republication or redistribution of Reuters content, including by caching, framing or similar means, is expressly prohibited without the prior written consent of Reuters. Reuters and the Reuters s
phere logo are registered trademarks and trademarks of the Reuters group of companies around the world.</CopyrightLine>
> <SlugLine>MARKETS-EURO-RATES</SlugLine>
> <NewsLine>
> <NewsLineType FormalName="Caption"/>
> <NewsLineText>MARKETS-EURO-RATES:Euro Rates at 1500 GMT - July 3</NewsLineText>
> </NewsLine>
> </NewsLines>
> <DescriptiveMetadata>
> <Language FormalName="en"/>
> <Genre FormalName="Table"/>
> <OfInterestTo FormalName="AFA"/>
> <OfInterestTo FormalName="CSA"/>
> <OfInterestTo FormalName="LBY"/>
> <OfInterestTo FormalName="RWSA"/>
> <OfInterestTo FormalName="RWS"/>
> <OfInterestTo FormalName="REULB"/>
> <OfInterestTo FormalName="GNS"/>
> <OfInterestTo FormalName="SXNA"/>
> <TopicOccurrence Importance="High" Topic="#ts_1"/>
> </DescriptiveMetadata>
> <ContentItem Duid="MTFH19813_2002-07-03_15-01-48_LOSAPJZA1_MAIN1_TEXT_CI">
> <MediaType FormalName="Text"/>
> <Format FormalName="XHTML"/>
> <Characteristics>
> <Property FormalName="ContentID" Value="urn:newsml:reuters.com:20020703:MTFH19813_2 002-07-03_15-01-48_LOSAPJZA1_TXT:1"/>
> <Property FormalName="ContentCreationDateAndTime" Value="20020703T150148+0000"/>
> <Property FormalName="USN" Value="LOSAPJZA1"/>
> <Property FormalName="Creator" Value="RTR_JANUS 2.300"/>
> </Characteristics>
> <DataContent>
> <html xmlns="http://www.w3.org/1999/xhtml">
> <head>
> <title/>
> </head>
> <body>
> <p> LONDON, July 3 (Reuters) - Following are the middle exchange rates for leading currencies against the euro:</p>
> <p> CURRENT HIGH LOW US Dollar 0.9803 0.9863 0.9767 Japanese Yen 117.48 118.23 117.29 British Pound 0.6423 0.6436 0.6408 Swiss Franc
1.4627 1.4664 1.461 Danish Crown 7.428 7.4292 7.428 Greek Drachma - - - Norwegian Crown 7.3362 7.3745 7.3306 Swedish Crown 9.102 9.13
4 9.058 Australian Dollar 1.7542 1.7591 1.7434 Canadian Dollar 1.5034 1.5046 1.4921 Hong Kong Dollar 7.6464 7.6779 7.6224 Russian Rouble 30.92 31.1 30.81 Singapore Doll
ar 1.7335 1.7418 1.731 </p>
> </body>
> </html>
> </DataContent>
> </ContentItem>
> </NewsComponent>
> </NewsComponent>
> </NewsItem>
> </NewsML>'
>
>
> DECLARE @.NewsMLPtr int
> EXEC sp_xml_preparedocument @.NewsMLPtr OUTPUT, @.NewsML
> SELECT *
> FROM OPENXML(@.NewsMLPtr, '/NewsML/NewsItem')
> WITH (
> Duid varchar(100) '@.Duid',
> RevisionId tinyint 'Identification/NewsIdentifier/RevisionId',
> FirstCreated varchar(100) 'NewsManagement/FirstCreated',
> Topic varchar(100) 'NewsComponent/TopicSet/Topic/Description'
> )
> EXEC sp_xml_removedocument @.NewsMLPtr
>

Export XML data to File

I have seen other questions like this, but none that provide the answer I need.

I need to move data from one SQL DB to another, located in different offices.

I want to do this programmatically as part of an application, ether using VB or T-SQL.

I understan how to use FOR XML to get the data into an XML column.

How do I save that to a file that I can move to a different machine?

I would prefer not to use bcp, so that if I do it in a stored proc, I do not have to use xp_cmdshell.

Thank you.

Richard

Hi Richard,

We dont have any tools that would allow you to export a table to XML and then have another table automatically consume it. There are a few ways to achieve this though. Here are a couple:

Option 1) You can generate a FOR XML statement for your source table. Save the results to an XML file, and then on the destination database you the XML Datatype's nodes() method to convert the xml into relational data, and insert it into your destination table. (Here is some documentation on the nodes() function. http://msdn2.microsoft.com/en-us/ms188282.aspx)

Option 2) You could fill a dataset with the relational data that you are interested in. Serialize the dataset as XML. Rehydarate another dataset with the xml and insert it into your destination tables.

Option 1 can be done using just T-SQL within SP's. Option 2 requires the use of .Net, but you could do it within an SP using SQLCLR in the engine.

|||

Here is code to write XML as destination. Variable uvRecordSet was created by Recordset Destination

Public Sub Main()

Dim adp As New OleDb.OleDbDataAdapter

Dim dt As New DataTable("WriteXMLTableName")

adp.Fill(dt, Dts.Variables("uvRecordSet").Value)

dt.WriteXml("C:\myxml.xml", False)

Dts.TaskResult = Dts.Results.Success

End Sub

Export XML data to File

I have seen other questions like this, but none that provide the answer I need.

I need to move data from one SQL DB to another, located in different offices.

I want to do this programmatically as part of an application, ether using VB or T-SQL.

I understan how to use FOR XML to get the data into an XML column.

How do I save that to a file that I can move to a different machine?

I would prefer not to use bcp, so that if I do it in a stored proc, I do not have to use xp_cmdshell.

Thank you.

Richard

Hi Richard,

We dont have any tools that would allow you to export a table to XML and then have another table automatically consume it. There are a few ways to achieve this though. Here are a couple:

Option 1) You can generate a FOR XML statement for your source table. Save the results to an XML file, and then on the destination database you the XML Datatype's nodes() method to convert the xml into relational data, and insert it into your destination table. (Here is some documentation on the nodes() function. http://msdn2.microsoft.com/en-us/ms188282.aspx)

Option 2) You could fill a dataset with the relational data that you are interested in. Serialize the dataset as XML. Rehydarate another dataset with the xml and insert it into your destination tables.

Option 1 can be done using just T-SQL within SP's. Option 2 requires the use of .Net, but you could do it within an SP using SQLCLR in the engine.

|||

Here is code to write XML as destination. Variable uvRecordSet was created by Recordset Destination

Public Sub Main()

Dim adp As New OleDb.OleDbDataAdapter

Dim dt As New DataTable("WriteXMLTableName")

adp.Fill(dt, Dts.Variables("uvRecordSet").Value)

dt.WriteXml("C:\myxml.xml", False)

Dts.TaskResult = Dts.Results.Success

End Sub

Export XML data - stored procedure

I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...
Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...

e.g Sample XML file required...
<Person>
<Name>Raymond</Name>
<NickName>The Legend</NickName>
</Person>
<Person>
<Name>Peter</Name>
<NickName>The King</NickName>
</Person>

sp_configure'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure'Web Assistant Procedures', 1;
GO
RECONFIGURE
GO
sp_makewebtask @.outputfile='C:\MyExportFile.xml',
@.query='SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS',
@.templatefile='C:\Template.tpl'

Try these two links for code samples including generating the XML file from a database table, this is assuming you are using SQL Server 2000. If you are using SQL Server 2005 all the code you need is in the BOL (books online) because XML is native to SQL Server 2005. Hope this helps.

http://forums.asp.net/1026295/ShowPost.aspx

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

Export valid XML files

Ladies and Gents,
I cannot seem to find an (easily) workable solution anywhere on the web.
All I need to do, is return the contents of some SQL server tables as XML
data sets.
I was able to form "proper" XML files by adding a top-level root element
(still unclear on why that is not built in). Now the issue that remains is
that SQL server will return the results with a ROWGUID and a full line
of undescores (_________________) before the actual XML.
I am trying to keep the process as simple as can get, avoiding SQLXML
and IIS -- I just want to find a solution where I can create a simple (!)
DTS package that will deliver a properly formatted XML file with a top
level element, one that IE will be able to display properly without
any further editing.
1) is this possible (anything is possible -- can this be done via some form
of "FOR XML" statement)
2) if there is no easy solution to 1, what methods would you recommend to
make the process
most maintanable? My requirements are simple -- I need to produce a
"feed" -- a set of properly formatted
XML files, based on many queries -- probably about 15-20 files
alltogether, so I need this to be
as simple as possible.
TIA,
EugeneCould you be more specific? Are you trying to export the database schema or
the data in your tables?
As simple as possible? A FOR XML query in a SQLXML template.
ML|||If you follow the "Microsoft" way, you set up a "web service" using the HTML
capabilities of SQL Server. The template supplies the formatting you need an
d
returns data in the proper format.
I do not have a link, but you can push a root tag into your FOR XML return.
The examples I have seen use the template, as well.
If the return is not valid, you can also throw the data through an XSLT
transform and get it to look however you desire. This is an extra step,
however.
NOTE: I am fairly fond of the whole SOA/web service concept, but it is not
for everyone. For SQL Server 2000, the link into IIS is not the best
implementation (HTTP endpoints in SQL 2005 are much better), but they provid
e
a means of moving towards SOA. SOA is not a silver bullet, but it makes a lo
t
of sense if you can make the paradigm change to working with messages instea
d
of CRUD.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Eugene" wrote:

> Ladies and Gents,
> I cannot seem to find an (easily) workable solution anywhere on the web.
> All I need to do, is return the contents of some SQL server tables as XML
> data sets.
> I was able to form "proper" XML files by adding a top-level root element
> (still unclear on why that is not built in). Now the issue that remains i
s
> that SQL server will return the results with a ROWGUID and a full line
> of undescores (_________________) before the actual XML.
> I am trying to keep the process as simple as can get, avoiding SQLXML
> and IIS -- I just want to find a solution where I can create a simple (!)
> DTS package that will deliver a properly formatted XML file with a top
> level element, one that IE will be able to display properly without
> any further editing.
> 1) is this possible (anything is possible -- can this be done via some for
m
> of "FOR XML" statement)
> 2) if there is no easy solution to 1, what methods would you recommend to
> make the process
> most maintanable? My requirements are simple -- I need to produce a
> "feed" -- a set of properly formatted
> XML files, based on many queries -- probably about 15-20 files
> alltogether, so I need this to be
> as simple as possible.
> TIA,
> Eugene
>
>|||I am trying to export the data.
Something along the lines of :
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||I am trying to export the data. Here's my query
SELECT
1 AS TAG
,NULL AS PARENT
,NULL AS [INSTITUTIONS!1]
,NULL AS [INSTITUTION!2!iconum]
,NULL AS [INSTITUTION!2!cusip]
,NULL AS [INSTITUTION!2!dlr]
,NULL AS [INSTITUTION!2!position]
,NULL AS [INSTITUTION!2!pct_held]
,NULL AS [INSTITUTION!2!position_change]
UNION
SELECT 2 AS TAG
,1 AS PARENT
,NULL AS [INSTITUTIONS!1]
,iconum AS [INSTITUTION!2!iconum]
,cusip AS [INSTITUTION!2!cusip]
,dlr AS [INSTITUTION!2!dlr]
,position AS [INSTITUTION!2!position]
,pct_held AS [INSTITUTION!2!pct_held]
,position_change AS [INSTITUTION!2!position_change]
FROM ##EUGENE_TEMP
ORDER BY [INSTITUTION!2!iconum]
FOR XML EXPLICIT
I get back something like :
XML_F52E2B61...
_____________________________
<INSTITUTIONS>
<INSTITUTION iconum="10134" cusip="151313103" dlr="2005-03-31T00:00:00"
position="22000" pct_held="0.002" position_change="22000"/>
</INSTITUTIONS>
I need to not have the top
XML_F52E2B61...
_____________________________.
SQLXML is not really an option now -- no ISS on the SQL box... Looks like
I'll just have to script a solution, unless
someone has another idea...
Thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||If you're using BCP to write the results to a text file or using QA's
"Results to file" option, you'll have to remove the unwanted pieces of text
either manually or programatically (not in T-SQL).
Whether you like it or not, you will need a client application to read the
result.
Oh, and another thing - XML results might get split into several rows of
data if not read as an XML by an appropriate client.
If installing IIS creates a possible breach of security, you can still use
the IIS lock-down tool to keep your network secure. The power of SQLXML is
immense, so why not use it?
ML

Export to/from XML

I have Database with the following structure: Regions -> Countries -> Customers -> Fabs -> Products. All relationships are one to many.
I need to build query that returns all data of any Region as XML.
What is the best way to do opposite action (Export from XML to DB) ?I just played with this over the weekend for the first time. Please take a look at "XML and Internet Support Overview" in SQL Server Books Online.

Once you configure this properly with IIS you can return auto generated XML from querying SQL Server.

What little I did, it was pretty cool.