Saving and manipulating XML data

Hi,
I am developing a HR type application and need some help trying to decide
what is best for the following situation.
I need to allow users to enter resume data in a web page. Once they have
entered all the data I would like to save it to a SQL Server database.
Here's the trick. I need to be able to retrieve the data from the database
and then manipulate it and allow the user to choose different resume styles
they would like to see the data displayed in. I also need to be able to
accept and provide (from or to an external source) this resume data in a
format that conforms to the HR-XML (www.hr-xml.org) standard.
So I figured I would build an interface that could take an xml file that is
HR-XML compliant and save the data to the database or vice versa. Then when
the user enters the data for their resume this data would be compiled into an
HR-XML compliant XML file and fed through the interface. If I was accepting
a file from an external source then that file would also be fed through the
same interface.
Does this make sense? I'm not new to XML, but I really haven't done much in
it yet, so I'm not sure if this is the best approach or not. Please feel
free to ask any questions you might need for clarification.
Thanks,
Wes
Hello, Wes!
You wrote on Tue, 19 Oct 2004 07:41:11 -0700:
W> I am developing a HR type application and need some help trying to
W> decide what is best for the following situation.
W> I need to allow users to enter resume data in a web page. Once they
W> have entered all the data I would like to save it to a SQL Server
W> database. Here's the trick. I need to be able to retrieve the data
W> from the database and then manipulate it and allow the user to choose
W> different resume styles they would like to see the data displayed in. I
W> also need to be able to accept and provide (from or to an external
W> source) this resume data in a format that conforms to the HR-XML
W> (www.hr-xml.org) standard.
You can produce xml document in the following manner
a) load all data from server to local recordset or dataset (ado.net) and
create xml manually, for example via XmlWriter
b) write complex and nontrivial "for xml explicit" query. In that case xml
document will be generated on the server without your intervention.
When you get xml document you will be able to apply different styles for
resume using xslt and css.
W> So I figured I would build an interface that could take an xml file that
W> is HR-XML compliant and save the data to the database or vice versa.
You can use annotated xml schema (AXD) to automaticaly map xml document to
relational schema.
W> Then when the user enters the data for their resume this data would be
W> compiled into an HR-XML compliant XML file and fed through the
W> interface.
It is good architectural decision to provide one way for incoming data to be
in database.
W> If I was accepting a file from an external source then that file would
W> also be fed through the same interface.
W> Does this make sense? I'm not new to XML, but I really haven't done
W> much in it yet, so I'm not sure if this is the best approach or not.
W> Please feel free to ask any questions you might need for clarification.
W> Thanks,
With best regards, Alex Shirshov.

Saving and loading rendered report

Is it possible that a rendered report can be saved with rendered data in a
file or stream or/ the xml format ?Additional info. I want to not only save the whole rendered report into a
file but also have a possibility to load the report from that file from
ReportViewer control.
What is the best way to implement it ?
"delpasso" wrote:
> Is it possible that a rendered report can be saved with rendered data in a
> file or stream or/ the xml format ?sql

save xml to file??

here is my code for extracting xml and saving it to a file, but i am not
having success in saving it to a file. can someone review and give
suggestions or help? am i on the right track?
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from customers for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "myconnection string"
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
adoStreamQuery.WriteText sSQL, adWriteChar
adoStreamQuery.Position = 0
oCmd.CommandStream = adostreamquery
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
adoStreamQuery.SaveToFile c:\xmlfile.xml
%>
i get the following error message:
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/Project2/test2.asp, line 26, column 28
adoStreamQuery.SaveToFile c:\xmlfile.xml
--^
please advise.The save to file parameter is a string - quotes around the file name might
help.
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
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:BBC5A090-E6AA-46A5-9AB2-C4B1D6C5E18C@.microsoft.com...
> here is my code for extracting xml and saving it to a file, but i am not
> having success in saving it to a file. can someone review and give
> suggestions or help? am i on the right track?
> <%
> Response.ContentType = "text/xml"
> Dim oCmd, sSQL
> sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" &
> _
> "select * from customers for xml auto, elements</sql:query></root>"
> Set oCmd = Server.CreateObject("ADODB.Command")
> oCmd.ActiveConnection = "myconnection string"
> Dim adoStreamQuery
> Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
> adoStreamQuery.Open
> adoStreamQuery.WriteText sSQL, adWriteChar
> adoStreamQuery.Position = 0
> oCmd.CommandStream = adostreamquery
> oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
> oCmd.Properties("Output Stream") = Response
> oCmd.Execute , , 1024
> adoStreamQuery.SaveToFile c:\xmlfile.xml
> %>
> i get the following error message:
> Error Type:
> Microsoft VBScript compilation (0x800A0400)
> Expected statement
> /Project2/test2.asp, line 26, column 28
> adoStreamQuery.SaveToFile c:\xmlfile.xml
> --^
> please advise.
>|||thanks a bunch. that worked and helped. =) however, it isn't saving the
results...it is saving the text of what sSQL is equal to. any ideas on how
to pipe the results to a file?
"Roger Wolter[MSFT]" wrote:

> The save to file parameter is a string - quotes around the file name might
> help.
> --
> 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
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:BBC5A090-E6AA-46A5-9AB2-C4B1D6C5E18C@.microsoft.com...
>
>

save xml to file??

here is my code for extracting xml and saving it to a file, but i am not
having success in saving it to a file. can someone review and give
suggestions or help? am i on the right track?
<%
Response.ContentType = "text/xml"
Dim oCmd, sSQL
sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"select * from customers for xml auto, elements</sql:query></root>"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "myconnection string"
Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open
adoStreamQuery.WriteText sSQL, adWriteChar
adoStreamQuery.Position = 0
oCmd.CommandStream = adostreamquery
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = Response
oCmd.Execute , , 1024
adoStreamQuery.SaveToFile c:\xmlfile.xml
%>
i get the following error message:
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/Project2/test2.asp, line 26, column 28
adoStreamQuery.SaveToFile c:\xmlfile.xml
--^
please advise.
The save to file parameter is a string - quotes around the file name might
help.
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
"snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
news:BBC5A090-E6AA-46A5-9AB2-C4B1D6C5E18C@.microsoft.com...
> here is my code for extracting xml and saving it to a file, but i am not
> having success in saving it to a file. can someone review and give
> suggestions or help? am i on the right track?
> <%
> Response.ContentType = "text/xml"
> Dim oCmd, sSQL
> sSQL = "<root><sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" &
> _
> "select * from customers for xml auto, elements</sql:query></root>"
> Set oCmd = Server.CreateObject("ADODB.Command")
> oCmd.ActiveConnection = "myconnection string"
> Dim adoStreamQuery
> Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
> adoStreamQuery.Open
> adoStreamQuery.WriteText sSQL, adWriteChar
> adoStreamQuery.Position = 0
> oCmd.CommandStream = adostreamquery
> oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
> oCmd.Properties("Output Stream") = Response
> oCmd.Execute , , 1024
> adoStreamQuery.SaveToFile c:\xmlfile.xml
> %>
> i get the following error message:
> Error Type:
> Microsoft VBScript compilation (0x800A0400)
> Expected statement
> /Project2/test2.asp, line 26, column 28
> adoStreamQuery.SaveToFile c:\xmlfile.xml
> --^
> please advise.
>
|||thanks a bunch. that worked and helped. =) however, it isn't saving the
results...it is saving the text of what sSQL is equal to. any ideas on how
to pipe the results to a file?
"Roger Wolter[MSFT]" wrote:

> The save to file parameter is a string - quotes around the file name might
> help.
> --
> 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
> "snickerskid" <snickerskid@.discussions.microsoft.com> wrote in message
> news:BBC5A090-E6AA-46A5-9AB2-C4B1D6C5E18C@.microsoft.com...
>
>

Save xml in sql

Hi...

I want to save xml string in sql, but, i have problem, the length of the string is 19,000 - 24000 chars, and nVarChar can contain 4000 chars, and binary can contain 8000 chars...

so, how can i solve this?

thank you...

I generally use a text field for storing html/xml info

|||

Hi there,

Use datatype Object to store that info.

Convert it into bytes, and store it on that Objecto field.

To retrieve it, obtain the bytes from the SQL Server, and convert to string again. It's the same way you strore files like pictures, etc..

gonzzas

|||

HI

You can use ntext type to store the string.

|||

Hi,

Beside the above solutions, you also can focus on SQL(Yukon). It provides a rich set of tools for storing and manipulating XML in databases, without treating it as a blob of binary data or an arbitrary string. When you want XML, just use the xml data type, which is a fundamental part of the product.

The link may be helpful to you. http://msdn.microsoft.com/msdnmag/issues/04/02/XMLinYukon/default.aspx

Thanks.

save XML in a table

Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?In SQL Server 2005, there is an XML datatype, which handles this quite well.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?|||But curious about SQL 2000 ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiU9scmJGHA.648@.TK2MSFTNGP14.phx.gbl...
> In SQL Server 2005, there is an XML datatype, which handles this quite
> well.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single
> row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>
>|||Check "XML best practices" in BOL.
In short, when you don't need xml data type you need blob. And when you need
identical copy of the XML string, such as legal document, you need blob. I
mean varchar(max) by blob.
Pohwan Han. Seoul. Have a nice day.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>|||Some of the problems:
-How will you search for data in the column/row within the XML?
-If the XML document is 50MB, how will you insert the data? OPENXML will
require you to load the document into a variable, using a huge amount of
resources.
-In a table with 50,000 rows, how do you identify each XML document?
"Han" wrote:

> Check "XML best practices" in BOL.
> In short, when you don't need xml data type you need blob. And when you ne
ed
> identical copy of the XML string, such as legal document, you need blob. I
> mean varchar(max) by blob.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
>|||If you want to do any of that, I really recommend to upgrade to SQL Server
2005. SQL Server 2000 was not designed for managing XML documents as a unit
but for enabling relational integration into XML.
Best regards
Michael
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:3427C6A2-CD47-486F-A21A-646CAB18662D@.microsoft.com...
> Some of the problems:
> -How will you search for data in the column/row within the XML?
> -If the XML document is 50MB, how will you insert the data? OPENXML will
> require you to load the document into a variable, using a huge amount of
> resources.
> -In a table with 50,000 rows, how do you identify each XML document?
> "Han" wrote:
>sql

save XML in a table

Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?
In SQL Server 2005, there is an XML datatype, which handles this quite well.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
Im not talking about storing the attributes of the elements of an XML file
into different columns of tables(s) but saving the entire XML into a single
row and column as a blob ?
What would be the pros and cons of doing it as such ?
|||But curious about SQL 2000 ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiU9scmJGHA.648@.TK2MSFTNGP14.phx.gbl...
> In SQL Server 2005, there is an XML datatype, which handles this quite
> well.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single
> row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>
>
|||Check "XML best practices" in BOL.
In short, when you don't need xml data type you need blob. And when you need
identical copy of the XML string, such as legal document, you need blob. I
mean varchar(max) by blob.
Pohwan Han. Seoul. Have a nice day.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
> Im not talking about storing the attributes of the elements of an XML file
> into different columns of tables(s) but saving the entire XML into a
> single row and column as a blob ?
> What would be the pros and cons of doing it as such ?
>
|||Some of the problems:
-How will you search for data in the column/row within the XML?
-If the XML document is 50MB, how will you insert the data? OPENXML will
require you to load the document into a variable, using a huge amount of
resources.
-In a table with 50,000 rows, how do you identify each XML document?
"Han" wrote:

> Check "XML best practices" in BOL.
> In short, when you don't need xml data type you need blob. And when you need
> identical copy of the XML string, such as legal document, you need blob. I
> mean varchar(max) by blob.
> --
> Pohwan Han. Seoul. Have a nice day.
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uLY662iJGHA.516@.TK2MSFTNGP15.phx.gbl...
>
|||If you want to do any of that, I really recommend to upgrade to SQL Server
2005. SQL Server 2000 was not designed for managing XML documents as a unit
but for enabling relational integration into XML.
Best regards
Michael
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:3427C6A2-CD47-486F-A21A-646CAB18662D@.microsoft.com...[vbcol=seagreen]
> Some of the problems:
> -How will you search for data in the column/row within the XML?
> -If the XML document is 50MB, how will you insert the data? OPENXML will
> require you to load the document into a variable, using a huge amount of
> resources.
> -In a table with 50,000 rows, how do you identify each XML document?
> "Han" wrote:

Save to XML file

i'm trying to save data to an XML file from an OLE DB source using the "For XML" clause in the SQL command. Do I need to use a flat file connection manager or a raw file connection manager? And what destination do I use?

jschroeder wrote:

i'm trying to save data to an XML file from an OLE DB source using the "For XML" clause in the SQL command. Do I need to use a flat file connection manager or a raw file connection manager? And what destination do I use?

There are no built in components to allow you to output as XML but Donald Farmer's book contains code showing you how to do it in a script component.

If you don't want to buy the book (although i recommend you do - its very cheap) then read this:

Output a file as a csv or an XML file
(http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx)

-Jamie

|||Save it to a flat file destination. It's just text, and you can give it whatever file name you want.|||I've been unsuccessful with that. I got an error that said the input of data type DT_IMAGE is not allowed. I tried using the data conversion component to convert to DT_TEXT, but got garbage in the output file.

save to xml

Hi,
is it possible to save the results from a query to an xml file directly from sql server (so without the use of any visual studio languages). What I would like to do is that when a user on our site completes an order, this order is saved as an xml file.
ThanksLook in BOL for the syntax in a SELECT

FOR XML AUTO

there are lots of options...

Never used it myself though|||yes, I know about the xml options. But those options format your query results. What I want to do is save these results to a physical xml file. The name of this file should be dynamically assigned. Do I use some kind of dts package or are there functionalities in for example SQLXM?|||bcp out a query or view from a stored procedure?|||Thanks Brett, thats what I thought. But perhaps I need to rephrase my question a bit: does anyone have some experience in using xml with SQL Server. What are the pros and cons? Did you use it directly or through an application?

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
MichelHello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||If you have SQL Server 2005, the below query is valid. And you'lll have more
features in FOR XML.
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:ujy1pp69GHA.4708@.TK2MSFTNGP05.phx.gbl...
> Hi
> How can I save the returned XML Expression into a variable? I tried to
> this a described on the next line, but with no luck...
> I use SQL Server 2000 SP 4
> DECLARE @.doc nvarchar(4000)
> SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
> auto)
> Thank's
> Michel
>

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
Michel
Hello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Save SQL XML field type to a xml file

Hi,

I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file?

Thanks

Bones

You can do by using CLR based stored procedure. Here is a link.

http://www.sqldbatips.com/showarticle.asp?ID=23

|||

Thanks for the help but unfortunitly this is for an other version on .NET. When I try to compile the .DLL I get the errors for "File.WriteAll" and "SqlContext.GetPipe();"

Both of which are not recognized. Any other ideas?

public static void WriteToFile(String content,String filename)
 {
 SqlPipe sqlP = SqlContext.GetPipe();
 try
 {
 File.WriteAll(filename, content + "\n");
 }
 catch(Exception ex)
 {
 sqlP.Send("Error writing to file : " + ex.Message);
 }
 }

|||

What is your dot net version ? i assume .Net 2.0 with sql 2005. above code will work with .net 2.0/sql 2005.

|||

Hi

I am using .NET 2.0 and SQL server 2005. There where some absolete properties in the post (e.g. GetPipe());

Here is the revised code (untested - but builds):

using System;using System.Data.Sql;using System.Data.SqlTypes;using System.IO;using System.Data.SqlClient;using Microsoft.SqlServer.Server;namespace XSLTCsharp{public class SQLCLRIO {public static void WriteToFile(String content, String filename) { SqlPipe sqlP = SqlContext.Pipe;try { File.WriteAllText(filename, content +"\n"); }catch (Exception ex) { sqlP.Send("Error writing to file : " + ex.Message); } } }}

|||

Here are couple of links that will help you to create a CLR based procedures.

http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx

http://www.c-sharpcorner.com/UploadFile/pk_khuman/ManagedStoredProceduresUsingCSharp02182007232059PM/ManagedStoredProceduresUsingCSharp.aspx

|||

Awesome...very helpfull. I got it to run. I had to do some tweaks with setting up the SQL database, but all is good!

thank a bunch.

Save SQL Query to XML

What's the best way to run a SQL query and save it to an XML file?

Use the For Xml clause in your Sql Query, and then route it to a Flat File destination.

For more information on For Xml: http://msdn2.microsoft.com/en-us/library/ms188273.aspx

|||That's easy enough. Thanks|||

Can you mark my post as Answered?

Thanks, it makes it easier to see which threads are pending, and which ones are answered.

save data from xml into database?

i have an xml file, it looks like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<shiporder orderid="889923" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>My Title</title>
<quantity>1</quantity>
<price>10.12</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>

how can i save this into a database(Sql) using a procedure

or..there is another way to do this?

im a newbie

There are couple of ways to do this, Load this XML into a dataset and use SQLBulkCopy feature to save it to the database or pass the whole XML to the Stored procedure and use XML feature of SQL server 2005 [look into the documentation ] and save it to the database.

|||

ok..i have this

DataSet dataset = new DataSet();

dataset.ReadXML("d:\\shiporder.xml",XMLReadMode.Auto)

SqlBulkCopy d = new SqlBulkCopy

what i need to do further?

it`s necesarry to create a destination database?

|||

i have something like this

DataSet dataset = new DataSet();

dataset.ReadXML("d:\\shiporder.xml",XMLReadMode.Auto);

SqlBulkCopy s = new SqlBulkCopy;

what i have to do nextt?

it`s necesarry to create a destination table?

please help:D

|||

DataTable dataTable = mydataset.Tables[0];

bulkCopy.DestinationTableName = yourtableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);

|||DataSet mydataset =newDataSet();

mydataset.ReadXml("d:\\shiporder.xml",XmlReadMode.Auto);

string cs ="DataSource=.\\SQLExpress; Integrated Security = True; Initial Catalog = Shiporder";

SqlConnection dest =newSqlConnection(cs);

DataTable dataTable = mydataset.Tables[0];

SqlBulkCopy bulkCopy =newSqlBulkCopy(dest);

bulkCopy.DestinationTableName ="dbo.ShipOrder";

bulkCopy.ColumnMappings.Clear();

foreach (DataColumn myColin dataTable.Columns)

bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);

bulkCopy.WriteToServer(dataTable);

it gives me this error:

WriteToSever requires an open and avaible Connection. The connection`s current state is closed.

how should i fixed this?
what`s wrong?

|||

Looks like you are not opening the connection.

DataSet mydataset =newDataSet();

mydataset.ReadXml("d:\\shiporder.xml",XmlReadMode.Auto);

string cs ="DataSource=.\\SQLExpress; Integrated Security = True; Initial Catalog = Shiporder";

SqlConnection dest =newSqlConnection(cs);

DataTable dataTable = mydataset.Tables[0];

SqlBulkCopy bulkCopy =newSqlBulkCopy(dest);

dest.Open();

bulkCopy.DestinationTableName ="dbo.ShipOrder";

bulkCopy.ColumnMappings.Clear();

foreach (DataColumn myColin dataTable.Columns)

bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);

bulkCopy.WriteToServer(dataTable);

Save any file into a table

I have a asp.net application where i can choose to save any file(could be jpg or dat or txt or xml etc etc) into a table.So far,i've manage to save it into an SQL server table in a column(set to Image datatype).But when i get the data from the table,put it into a byte array and recreate it.I notice that the files can be recreated correctly if they are of type .txt files.But jpg files can't be recreated and trying to open the jpg files gives an error.I don't think it is the file saving that is the problem since any files can be save.Rather it is the file recreating that is the problem.Can anyone help?Are you specifying the ContentType for the page?|||Yup,the content type is saved into the table as a seperate column too.As i need to return the results of the sql statement getting the data,content type,name etc in a dataset.I using ascii encoding to code the hex data(the file itself) into a string,insert it into a dataset and pass it back.The problem is after i get the string(which is actually hex data encoded into a string).How do i recreate the file data from the string?My guess is that i need to convert the string to hex,then to binary and do a binary write?Where do i set the content type anyway since i don't think writing it to response is correct.|||Thw worst part is that this is a console application and i can't figure out how to get hold of the contenttype property before writing my file from my table.