converting date format on for xml query

im trying to convert date format with this with no sucess
http://myserver/?sql=select%20*%20,CONVERT(datetime,DATA,3)
as%20[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%
20AUTO&root=root
it order by date, but dont convert the date format...any
help ?
What is the date formay you want to get? Note that all datetime values will
mapped to an ISO format. If you want your own format, CONVERT to a string in
your select statement.
Michael
"max" <anonymous@.discussions.microsoft.com> wrote in message
news:7df801c43160$6ca153b0$a001280a@.phx.gbl...
> im trying to convert date format with this with no sucess
> http://myserver/?sql=select%20*%20,CONVERT(datetime,DATA,3)
> as%20[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%
> 20AUTO&root=root
> it order by date, but dont convert the date format...any
> help ?
|||hi
thanks
im trying to get the datetime field that on my sql is on
this format
3/5/2004 14:30:18
but on xml it shows me this
2004-05-03T14:30:18
and i want it to return me
DD/MM/YY HH:MM:SS
how could it be done
max

>--Original Message--
>What is the date formay you want to get? Note that all
datetime values will
>mapped to an ISO format. If you want your own format,
CONVERT to a string in
>your select statement.
>Michael
>"max" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7df801c43160$6ca153b0$a001280a@.phx.gbl...
sucess[vbcol=seagreen]
(datetime,DATA,3)
>
>.
>
|||its impossible or something ?
thanks
max
[vbcol=seagreen]
>--Original Message--
>hi
>thanks
>im trying to get the datetime field that on my sql is on
>this format
>3/5/2004 14:30:18
>but on xml it shows me this
>2004-05-03T14:30:18
>and i want it to return me
>DD/MM/YY HH:MM:SS
>how could it be done
>
>max
>
>datetime values will
>CONVERT to a string in
>message
>sucess
>(datetime,DATA,3)
format...any
>.
>
|||Here you are:
create table t (d datetime)
go
insert into t values ('3/5/2004 14:30:18')
go
select CONVERT(nvarchar(50), d, 101)+' '+CONVERT(nvarchar(50), d, 108) as d
from t for xml auto
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:7ada01c43164$b6cf3520$a401280a@.phx.gbl...[vbcol=seagreen]
> hi
> thanks
> im trying to get the datetime field that on my sql is on
> this format
> 3/5/2004 14:30:18
> but on xml it shows me this
> 2004-05-03T14:30:18
> and i want it to return me
> DD/MM/YY HH:MM:SS
> how could it be done
>
> max
>
> datetime values will
> CONVERT to a string in
> message
> sucess
> (datetime,DATA,3)
sqlsql

Converting Crystal Reports 8.5 XML output to RDL....

All--

We have several dozen Crystal (8.5) Reports that we would like to port to SSRS 2005. I believe that I can save these reports as XML data files with an accompanying schema. We are not interested in the data, but would like to be able to preserve the reports' layout from one reporting platform to another. I believe I might be able to write an XSLT transformation to convert the Crystal XML output into RDL, but this is not a trivial task. (We would like to avoid, at all costs, manually redesigning the reports in SSRS, but, if it comes to that, we will have to bite the bullet.) Has such an XSLT transformation already been created, and, if so, would anyone care to share their work?

TIA,

mattyseltz in NY

You could try this service for $25 per report:

http://rpttosql.com/

cheers,

Andrew

sqlsql

Converting Crystal Reports 8.5 XML output to RDL....

All--

We have several dozen Crystal (8.5) Reports that we would like to port to SSRS 2005. I believe that I can save these reports as XML data files with an accompanying schema. We are not interested in the data, but would like to be able to preserve the reports' layout from one reporting platform to another. I believe I might be able to write an XSLT transformation to convert the Crystal XML output into RDL, but this is not a trivial task. (We would like to avoid, at all costs, manually redesigning the reports in SSRS, but, if it comes to that, we will have to bite the bullet.) Has such an XSLT transformation already been created, and, if so, would anyone care to share their work?

TIA,

mattyseltz in NY

You could try this service for $25 per report:

http://rpttosql.com/

cheers,

Andrew

converting an xml to html

Hi ,
I am a beginner. I AM trying to convert an xml fie into html :
<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- Edited with XML Spy v2007 (http://www.altova.com) -->
<CATALOG>
<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
<CD>
<TITLE>Hide your heart</TITLE>
<ARTIST>Bonnie Tyler</ARTIST>
<COUNTRY>UK</COUNTRY>
<COMPANY>CBS Records</COMPANY>
<PRICE>9.90</PRICE>
<YEAR>1988</YEAR>
</CD>
</CATALOG>I thought i should first create a DTD for this .Is that
true?
Please help me get started.
-Shyam
Just create an XSLT stylesheet. Here are some examples.
http://www.topxml.com/xsltstylesheets/
"shyamali" <shyamalibala@.gmail.com> wrote in message
news:1172074617.679808.198740@.j27g2000cwj.googlegr oups.com...
> Hi ,
> I am a beginner. I AM trying to convert an xml fie into html :
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <!-- Edited with XML Spy v2007 (http://www.altova.com) -->
> <CATALOG>
> <CD>
> <TITLE>Empire Burlesque</TITLE>
> <ARTIST>Bob Dylan</ARTIST>
> <COUNTRY>USA</COUNTRY>
> <COMPANY>Columbia</COMPANY>
> <PRICE>10.90</PRICE>
> <YEAR>1985</YEAR>
> </CD>
> <CD>
> <TITLE>Hide your heart</TITLE>
> <ARTIST>Bonnie Tyler</ARTIST>
> <COUNTRY>UK</COUNTRY>
> <COMPANY>CBS Records</COMPANY>
> <PRICE>9.90</PRICE>
> <YEAR>1988</YEAR>
> </CD>
> </CATALOG>I thought i should first create a DTD for this .Is that
> true?
> Please help me get started.
> -Shyam
>
|||On Feb 21, 11:16 am, "shyamali" <shyamalib...@.gmail.com> wrote:
> Hi ,
> I am a beginner. I AM trying to convert an xml fie into html :
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <!-- Edited with XML Spy v2007 (http://www.altova.com) -->
> <CATALOG>
> <CD>
> <TITLE>Empire Burlesque</TITLE>
> <ARTIST>Bob Dylan</ARTIST>
> <COUNTRY>USA</COUNTRY>
> <COMPANY>Columbia</COMPANY>
> <PRICE>10.90</PRICE>
> <YEAR>1985</YEAR>
> </CD>
> <CD>
> <TITLE>Hide your heart</TITLE>
> <ARTIST>Bonnie Tyler</ARTIST>
> <COUNTRY>UK</COUNTRY>
> <COMPANY>CBS Records</COMPANY>
> <PRICE>9.90</PRICE>
> <YEAR>1988</YEAR>
> </CD>
> </CATALOG>I thought i should first create a DTD for this .Is that
> true?
> Please help me get started.
> -Shyam
And here are some XSLT Tools to design your stylesheet:
http://www.stylusstudio.com/xslt.html
Download free trial from: http://www.stylusstudio.com/xml_download.html
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.com
sqlsql

converting an xml to html

Hi ,
I am a beginner. I AM trying to convert an xml fie into html :
<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- Edited with XML Spy v2007 (http://www.altova.com) -->
<CATALOG>
<CD>
<TITLE>Empire Burlesque</TITLE>
<ARTIST>Bob Dylan</ARTIST>
<COUNTRY>USA</COUNTRY>
<COMPANY>Columbia</COMPANY>
<PRICE>10.90</PRICE>
<YEAR>1985</YEAR>
</CD>
<CD>
<TITLE>Hide your heart</TITLE>
<ARTIST>Bonnie Tyler</ARTIST>
<COUNTRY>UK</COUNTRY>
<COMPANY>CBS Records</COMPANY>
<PRICE>9.90</PRICE>
<YEAR>1988</YEAR>
</CD>
</CATALOG>I thought i should first create a DTD for this .Is that
true?
Please help me get started.
-ShyamJust create an XSLT stylesheet. Here are some examples.
http://www.topxml.com/xsltstylesheets/
"shyamali" <shyamalibala@.gmail.com> wrote in message
news:1172074617.679808.198740@.j27g2000cwj.googlegroups.com...
> Hi ,
> I am a beginner. I AM trying to convert an xml fie into html :
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <!-- Edited with XML Spy v2007 (http://www.altova.com) -->
> <CATALOG>
> <CD>
> <TITLE>Empire Burlesque</TITLE>
> <ARTIST>Bob Dylan</ARTIST>
> <COUNTRY>USA</COUNTRY>
> <COMPANY>Columbia</COMPANY>
> <PRICE>10.90</PRICE>
> <YEAR>1985</YEAR>
> </CD>
> <CD>
> <TITLE>Hide your heart</TITLE>
> <ARTIST>Bonnie Tyler</ARTIST>
> <COUNTRY>UK</COUNTRY>
> <COMPANY>CBS Records</COMPANY>
> <PRICE>9.90</PRICE>
> <YEAR>1988</YEAR>
> </CD>
> </CATALOG>I thought i should first create a DTD for this .Is that
> true?
> Please help me get started.
> -Shyam
>|||On Feb 21, 11:16 am, "shyamali" <shyamalib...@.gmail.com> wrote:
> Hi ,
> I am a beginner. I AM trying to convert an xml fie into html :
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <!-- Edited with XML Spy v2007 (http://www.altova.com) -->
> <CATALOG>
> <CD>
> <TITLE>Empire Burlesque</TITLE>
> <ARTIST>Bob Dylan</ARTIST>
> <COUNTRY>USA</COUNTRY>
> <COMPANY>Columbia</COMPANY>
> <PRICE>10.90</PRICE>
> <YEAR>1985</YEAR>
> </CD>
> <CD>
> <TITLE>Hide your heart</TITLE>
> <ARTIST>Bonnie Tyler</ARTIST>
> <COUNTRY>UK</COUNTRY>
> <COMPANY>CBS Records</COMPANY>
> <PRICE>9.90</PRICE>
> <YEAR>1988</YEAR>
> </CD>
> </CATALOG>I thought i should first create a DTD for this .Is that
> true?
> Please help me get started.
> -Shyam
And here are some XSLT Tools to design your stylesheet:
http://www.stylusstudio.com/xslt.html
Download free trial from: http://www.stylusstudio.com/xml_download.html
Sincerely,
The Stylus Studio Team
http://www.stylusstudio.com

Converting a file into multiple tables

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)

It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.

But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.

--
Wes Groleau

If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:K7x6h.10720$l%2.2462@.trnddc05...

Quote:

Originally Posted by

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)
>
It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.
>
But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.
>
--
Wes Groleau
>
If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?|||You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:PCH6h.708$Fg.683@.tornado.socal.rr.com...

Quote:

Originally Posted by

Dan Guzman wrote:
>

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

|||Dan Guzman wrote:

Quote:

Originally Posted by

You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

Of course, I could easily write something to
convert it to XML that SQL server can read.
But then I could just as easily convert it
directly into INSERT statements. I'm just
wondering whether DTS or anything else is faster.

I already have a tool that loads the entire
file into an array of lines and provides various
query functions for other apps to access it.

But I'd like to put multiple files in the database
instead of having to select one file at a time.

By the way, whatever the technique is, it could
probably also handle GEDCOM files.

--
Wes Groleau

There ain't no right wing,
there ain't no left wing.
There's only you and me and we just disagree.
(apologies to Jim Krueger)|||Ed Murphy wrote:

Quote:

Originally Posted by

Dan Guzman wrote:

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

I think I figured out a solution (haven't tried it yet).

Comments on this idea welcome (I'm kind of new to SQL):

The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1

So if a file has (data elem delims changed to spaces)
....
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
....
then the first pass through the file could create rows

.... A B C X Y Z ...
.... A B C 1 2 3 ...
.... D E F P Q R ...
.... D E F 5 6 7 ...

Next, one query could SELECT DISTINCT to give

.... A B C
.... D E F

while another could SELECT for

.... A X Y Z ...
.... A 1 2 3 ...
.... D P Q R ...
.... D 5 6 7 ...

and the same strategy could be used on each adjacent pair of levels.

Right ?

--
Wes Groleau

He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin|||It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you can
leverage a high-performance XML import utility like SQLXML without writing
additional code. Although it will take a while, I expect XML will
eventually replace both EDI and GEDCOM formats. You'll be a step ahead if
you can process XML too.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:uJv7h.211$9e.25@.trnddc02...

Quote:

Originally Posted by

Ed Murphy wrote:

Quote:

Originally Posted by

>Dan Guzman wrote:

Quote:

Originally Posted by

>>Take a look at SQLXML Bulk Load
>>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>>
>He'd also need an EDI to XML translator. (I recognize those
>damnable start/end tags.) Google indicates that several
>translators exist; anyone want to offer a recommendation?

>
I think I figured out a solution (haven't tried it yet).
>
Comments on this idea welcome (I'm kind of new to SQL):
>
The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1
>
So if a file has (data elem delims changed to spaces)
...
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
...
then the first pass through the file could create rows
>
... A B C X Y Z ...
... A B C 1 2 3 ...
... D E F P Q R ...
... D E F 5 6 7 ...
>
Next, one query could SELECT DISTINCT to give
>
... A B C
... D E F
>
while another could SELECT for
>
... A X Y Z ...
... A 1 2 3 ...
... D P Q R ...
... D 5 6 7 ...
>
and the same strategy could be used on each adjacent pair of levels.
>
Right ?
>
--
Wes Groleau
>
He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin

|||Dan Guzman wrote:

Quote:

Originally Posted by

It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you
can leverage a high-performance XML import utility like SQLXML without
writing additional code. Although it will take a while, I expect XML
will eventually replace both EDI and GEDCOM formats. You'll be a step
ahead if you can process XML too.

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?

I got the impression when I was reading about it that one
XML file makes one table and vice versa.

--
Wes Groleau
----

"Thinking I'm dumb gives people something to
feel smug about. Why should I disillusion them?"
-- Charles Wallace
(in _A_Wrinkle_In_Time_)|||Wes Groleau wrote:

Quote:

Originally Posted by

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?
>
I got the impression when I was reading about it that one
XML file makes one table and vice versa.

The impression was wrong. I studied the MS KB article cited
earlier, and I can easily make such XML files. Only, the process
of transforming the file into XML is similar to the process used
by bulk load to turn the XML into records. So I suspect it would
add a little speed if I went directly to records.

--
Wes Groleau

Words of the Wild Wes(t) = http://ideas.lang-learn.us/WWWsqlsql

Converting a Column in XML

Hello NG!
We would like to convert an Column which contain XMLs to an XML Column.
Actually it ist formatted as plain Text in a big Table with approx. 300.000
Lines.
Trying to convert the Type of the Column to XML does not succed because of a
TimeoutError everytime we try it.
Do someone has an idea how we can solve this Problem?
Thank xou very much
MarkusHave you tried exporting/splitting your table into say, 3 or 6 equal parts
as physical temp tables, convert your field into XML there, and then
re-import into the orginal table? You might need to drop some FKs to do
this.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Markus Nistim" <manistim@.hotmail.com> wrote in message
news:%23ZqoniMNIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hello NG!
> We would like to convert an Column which contain XMLs to an XML Column.
> Actually it ist formatted as plain Text in a big Table with approx.
> 300.000 Lines.
> Trying to convert the Type of the Column to XML does not succed because of
> a TimeoutError everytime we try it.
> Do someone has an idea how we can solve this Problem?
> Thank xou very much
> Markus
>

Convert XML Date in T-SQL

Our persistent objects use an internal dataset to hold their data. When I
save to SQL Server 2000, I do so in one call to the database sending the XML
representation output from the DataSet.GetXml() method and shredding the XML
values into their tables & columns via T-SQL. Everything works great, except
for converting the default XML DateTime output from the DataSet. The output
is like this: 2004-10-20T16:00:00.0000000-05:00. But the list of compatible
T-SQL formats accepted by CONVERT does not show this format (List found here
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp).
The closest format I can see is Style 126: yyyy-mm-dd Thh:mm:ss.mmm. If I
pass the default DataSet XML date format to CONVERT it throws an error. What
I’m currently doing is pre-processing the XML string before sending it to the
database. I iterate through each known date field and convert the value
before sending to SQL Server. This seems unnecessary, but it also seems
unnecessary that the default ADO XML date output is by all appearances
incompatible with SQL Server’s T-SQL CONVERT function. What am I missing
here? Know any ideas?
Hello, MPalmer78!
You wrote on Thu, 21 Oct 2004 09:24:23 -0700:
[Sorry, skipped]
See KB811767.
With best regards, Alex Shirshov.

Convert word doc to XML and store and retrive form SQL 2000

Hi All,
i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .
Thanks in Advance .
Regards ,
raja

why do they need to be stored in a database? can't they just be stored as files - maybe index them in the database?

Regards

|||

I agree, why destroy those data caches with big useless blobs?

convert table data into complex xml document

I would like to convert the following two tables into the XML document below
without a bunch of UNIONS. The reason being in my case i have hundereds of
tables w/ different fields all going into a single XML document. Is there
any way to use user defined functions or something to construct just one XML
document? Or is there a way i can convert all the foos into a string, all
the bars into a string etc. then concatinate them and return them as an xml
document?
TABLE FOO
x y
________________ ____________________
1 11
2 22
TABLE BAR
a b
________________ ____________________
3 33
4 44
Using Open XML how do you convert this into:
<rootfoobar>
<foos>
<foo x='1' y='11'/>
<foo x='2' y='22'/>
</foos>
<bars>
<bar a='3' b='33'/>
<bar a='4' b='44'/>
</bars>
</rootfoobar>
The UNIONS are not so bad (the mainteance of the query is rather ugly
though).
In SQL Server 2000, you either use the mapping schemas of the mid-tier
SQLXML component or FOR XML EXPLICIT queries.
If the data has no correlation, you can also use the XML templates in SQLXML
to put individual query results together.
In SQL Server 2005, you will be able to nest FOR XML and use the new PATH
mode that avoids the UNION all. See my blog entries and links to an MSDN
whitepaper on http://sqljunkies.com/weblog/mrys
HTH
Michael
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23l2gkbrbEHA.3716@.TK2MSFTNGP11.phx.gbl...
>I would like to convert the following two tables into the XML document
>below
> without a bunch of UNIONS. The reason being in my case i have hundereds of
> tables w/ different fields all going into a single XML document. Is there
> any way to use user defined functions or something to construct just one
> XML
> document? Or is there a way i can convert all the foos into a string, all
> the bars into a string etc. then concatinate them and return them as an
> xml
> document?
> TABLE FOO
> x y
> ________________ ____________________
> 1 11
> 2 22
>
>
> TABLE BAR
> a b
> ________________ ____________________
> 3 33
> 4 44
>
> Using Open XML how do you convert this into:
> <rootfoobar>
> <foos>
> <foo x='1' y='11'/>
> <foo x='2' y='22'/>
> </foos>
> <bars>
> <bar a='3' b='33'/>
> <bar a='4' b='44'/>
> </bars>
> </rootfoobar>
>
>

Convert SQL table data to XML format

Hello,

I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?

Thanks all in advance,
SauravHello,

I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?

Thanks all in advance,
Saurav

Check BOL for FOR XML Clause|||if you need some structure to your xml (and because you are on 2005), you can use FOR XML PATH, which is infinitely better than the (horrible) 2000 alternative, FOR XML EXPLICIT.

don't use FOR XML EXPLICIT. it's impossible to maintain any but the simplest queries written with it.

convert sql selects into xml format

hai all,

I need some help and is very urget. I wan to convert the output of the sql query into xml format but i don hv any idea on how to do it. I'm using vb.net for this application.

Hope your guys out there can help me on this.

Regards,

KYY

Read up on the FOR XML clause that SQL Server supports: http://msdn2.microsoft.com/en-us/library/ms191268.aspx

Convert Ntext type to XML type

I have a ntext column which stores rows of xml data.

Ive decided that the best way to query the xml data is to Convert the ntext data into xml data type and use Sql Server 2005 builtin Xml Query tools to perform FLWOR expressions. The problem Im having is getting the ntext data Converted to xml.

When I try to convert the ntext text, I of course get an error that local variables of ntext type are invalid.

Any suggestions on a way I might accomplish this?

Thanks, -lance

Hi,

From your description, it seems that you met trouble while you are going convert the field type from ntext to xml, right?

Based on my understanding , there are several factors which may cause the failure of the converting. Please make sure that you are not using "UTF-8" for your XML encoding in your ntext field. You can use "UTF-16" instead.

Another factor is you should make sure that the XML schema is right and the tags are in the right format, otherwise, it would also cause the failure of the converting.

Thanks.

Convert NTEXT to XML for use in following query.

[Apologies for the cross-post]
Hi,
I have the following query in a stored procedure, where @.In_IDs is of type
XML:
SELECT Images.IsCompressed,
Images._Timestamp
FROM
Images
CROSS APPLY
@.In_IDs.nodes('//id') AS T(nref)
WHERE
Images.ID_Adjacency = nref.value('.', 'int')
However, sometimes I'm sending a lot of IDs (possibly a few thousand) and
the resulting XML document seems to become truncated. i.e. if I send 1,000,
I will get back 950 records. What I would like to do is pass in an NTEXT
field and convert this to XML in order to do the join on the full set. Any
ideas how I do this?
Thanks
RobinHello Robin,

> However, sometimes I'm sending a lot of IDs (possibly a few thousand)
> and the resulting XML document seems to become truncated. i.e. if I
> send 1,000, I will get back 950 records. What I would like to do is
> pass in an NTEXT field and convert this to XML in order to do the join
> on the full set. Any ideas how I do this?
Here's one way to do that.
use scratch
go
create table dbo.objects(id int,descr nvarchar(200))
create table dbo.ids(list ntext)
go
insert into dbo.objects values (1,'apple')
insert into dbo.objects values (2,'banana')
insert into dbo.objects values (3,'cherry')
insert into dbo.objects values (4,'durian')
go
insert into dbo.ids(list) values ('<ids><id>1</id><id>2</id><id>3</id><id>4<
/id></ids>')
go
declare @.x xml
select @.x = list from dbo.ids
;with l(id) as (select t.c.value('.','int')
from @.x.nodes('//id') as t(c))
select l.id,o.descr from l join dbo.objects o on l.id = o.id
go
drop table dbo.ids
drop table dbo.objects
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent, thanks for your response. I think some misunderstanding here
though with your answer - I can't quite see how to make that work. I have
the following (below). My problem is with the truncation of @.In_IDs (the
XML parameter). I want to replace it with an NTEXT parameter and then
somehow prepare it as an XML document in order to perform the query. I'm
passing in an XML list of ID's and want to return the record for each of
those IDs but I think the limit on the size of the XML document prevents the
code below from working when the number of ID's is largeish.
Robin
CREATEPROCEDURE [dbo].[Image_Get_Set_Details]
@.In_IDs XML /* The IDs of the image whose details we want to fetch
*/
/*
Procedure expects XML in the following format:
<query>
<id>1023</id>
<id>1024</id>
<id>1025</id>
</query>
*/
AS
SELECT Images.ID_Adjacency,
Images.IsCompressed,
Images._Timestamp
FROM
Images
CROSS APPLY
@.In_IDs.nodes('//id') AS T(nref)
WHERE
Images.ID_Adjacency = nref.value('.', 'int')|||Hello Robin,
The XML DataType supports instances up to two 2gb so that seems unlikely.
NTEXT does the same thing, but it is depreciated so I'd avoid using it. Here
's
an example that does essentially the same work your looking for. It works
for me for lists from 100 to 1000000 elements.
use scratch
go
create table dbo.test(id int identity(1,1) primary key clustered,v int)
go
set nocount on
declare @.l int
set @.l = 100000
while @.l > 0
begin
insert into dbo.test values (@.l)
set @.l = @.l-1
end
go
create procedure dbo.GetVs(@.tlist nvarchar(max))
as begin
set nocount on
declare @.list xml
set @.list = @.tlist
select @.list.value('count(//id)','int') as [count],datalength(@.list) as
[size]
select t.c.value('.','int') as ID,t1.v
from dbo.test t1
cross apply @.list.nodes('//id') as t(c)
where t1.id = t.c.value('.','int')
end
go
declare @.olist nvarchar(max)
select @.olist = convert(nvarchar(max),( select v as id from dbo.test order
by v for xml path(''),root('query'),type))
exec dbo.GetVs @.olist
go
drop table dbo.test
drop proc dbo.GetVs
go
Note that converting the list to nvarchar(max) isn't required as all. This
also works:
use scratch
go
create table dbo.test(id int identity(1,1) primary key clustered,v int)
go
set nocount on
declare @.l int
set @.l = 100000
while @.l > 0
begin
insert into dbo.test values (@.l)
set @.l = @.l-1
end
go
create procedure dbo.GetVs(@.list xml)
as begin
set nocount on
select @.list.value('count(//id)','int') as [count],datalength(@.list) as
[size]
select t.c.value('.','int') as ID,t1.v
from dbo.test t1
cross apply @.list.nodes('//id') as t(c)
where t1.id = t.c.value('.','int')
end
go
declare @.list xml
select @.list = ( select v as id from dbo.test order by v for xml path(''),ro
ot('query'),type)
exec dbo.GetVs @.list
go
drop table dbo.test
drop proc dbo.GetVs
go
What you might want to do is test that you're getting all the nodes in the
list that you think you are, as I do with the XQuery count select.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent. It turns out I had a bug somewhere which lead me to believe
there was a limit on XML parameter size, totally unrelated to my sproc.
Sorry to have wasted your time.
Robin

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert Microsoft Word XML to PDF without using Microsoft Word

Hi all,

I work for a financial company where we send out lots of correspondence to clients on a daily and monthly basis. This would typically be something like a financial report, account statements, etc. We've decided to use PDF as the format which these documents must be in when our clients receive them.

Our marketing and sales departments designs the templates of these documents using Microsoft Word. They can save these MS Word documents in the Word XML format (either 2003 or 2007). We want it in XML format, because it is text based, and we can therefore string replace the content to update the template with a client’s information.In the same token RTF would also do, but the file size when converted to RTF is a problem.

What I am looking for is a way to convert this XML into PDF using something like XSL-FO - i.e. we do not want to use the Word Interopt on our Production Server, firstly it is a little slow and secondly the production server is unmanned and we cannot allow a WinWord.EXE process to not close successfully for some or other reason.

I've tried a number of companies' evaluation software to try and achieve this (converting the WordML to PDF via XSL-FO), but so far no luck.

Does anybody know of a stylesheet out there with which I will be able to achieve this? Or an alternative method of converting MS Word files into PDF without using Word to do it?

Thank you in advance.

Regards

This really isn't the right forum for this question. You might consider one of the Office forums located here.

Cheers,
-Isaac

Convert from unicodestring to uniqueidentifier

Hello,
I have a XML data source containing strings representing GUIDs (ie <modelAsset id="e04ba23c-0401-43a9-b40c-b4c3d580caa9" />) and I need to fill a SQL table with uniqueidentifier column.

I added Data Conversion component setting the data type as DT_GUID.

Running the package I get the following error message:

Error: 0xC02020C5 at Import MyALM settings, Data Conversion [1301]: Data conversion failed while converting column "id" (4645) to column "GUID" (5912). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Import MyALM settings, Data Conversion [1301]: The "output column "GUID" (5912)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "GUID" (5912)" specifies failure on error. An error occurred on the specified object of the specified component.

How can I solve the problem ?

Thanks,
PierreWhile the error message could use some improvement the problem is that your strings that represent guids are not exactly correct. A string representation of a GUID has opening and closing curly braces ({}) and yours do not. If you wrap the column with curly braces (you could use a derived column for this) then your conversion should succeed.

HTH,
Matt|||

Thanks - I had the same problem.

It would be useful if the documentation for uniqueidentifer was updated to reflect the need for { and }.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b026035b-f3d2-4d70-989d-3884b4ca0233.htm

Currently it states:

"converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value."

|||

So did you use the Send Feedback link then?

-Jamie

Convert from unicodestring to uniqueidentifier

Hello,
I have a XML data source containing strings representing GUIDs (ie <modelAsset id="e04ba23c-0401-43a9-b40c-b4c3d580caa9" />) and I need to fill a SQL table with uniqueidentifier column.

I added Data Conversion component setting the data type as DT_GUID.

Running the package I get the following error message:

Error: 0xC02020C5 at Import MyALM settings, Data Conversion [1301]: Data conversion failed while converting column "id" (4645) to column "GUID" (5912). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Import MyALM settings, Data Conversion [1301]: The "output column "GUID" (5912)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "GUID" (5912)" specifies failure on error. An error occurred on the specified object of the specified component.

How can I solve the problem ?

Thanks,
PierreWhile the error message could use some improvement the problem is that your strings that represent guids are not exactly correct. A string representation of a GUID has opening and closing curly braces ({}) and yours do not. If you wrap the column with curly braces (you could use a derived column for this) then your conversion should succeed.

HTH,
Matt|||

Thanks - I had the same problem.

It would be useful if the documentation for uniqueidentifer was updated to reflect the need for { and }.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b026035b-f3d2-4d70-989d-3884b4ca0233.htm

Currently it states:

"converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value."

|||

So did you use the Send Feedback link then?

-Jamie