473,376 Members | 1,009 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,376 software developers and data experts.

Store XML in SQL Server Table

Hi

I wanted to store xml in a SQL Server Table

How does one go about it????

How does the Insert look like??? If I do it from a textfile or xmldocument?

How doe the Select look like if I want to put back into an xml document ??

THanks
Jun 27 '08 #1
6 2364
sippyuconn wrote:
I wanted to store xml in a SQL Server Table

How does one go about it????

How does the Insert look like??? If I do it from a textfile or xmldocument?

How doe the Select look like if I want to put back into an xml document ??
Do you want to store one XML document in:
- multiple fields ?
- single field of type NTEXT ?
- single field of type XML ?

Arne
Jun 27 '08 #2


"Arne Vajhøj" wrote:
sippyuconn wrote:
I wanted to store xml in a SQL Server Table

How does one go about it????

How does the Insert look like??? If I do it from a textfile or xmldocument?

How doe the Select look like if I want to put back into an xml document ??

Do you want to store one XML document in:
- multiple fields ?
- single field of type NTEXT ?
- single field of type XML ?

Arne
Hi

I have small xmlfiles and each row would be a different file

So I was wondering do I insert as a textfile,stream or read xml into an
xmldocument and somehow do the insert???

Also on the select - how do I retrieve and what do I put the xml into??
a string ??? or into an xmldocument??

Thanks
Jun 27 '08 #3
sippyuconn wrote:
>
"Arne Vajhøj" wrote:
>sippyuconn wrote:
>>I wanted to store xml in a SQL Server Table

How does one go about it????

How does the Insert look like??? If I do it from a textfile or xmldocument?

How doe the Select look like if I want to put back into an xml document ??
Do you want to store one XML document in:
- multiple fields ?
- single field of type NTEXT ?
- single field of type XML ?
I have small xmlfiles and each row would be a different file
Is that 1, 2 or 3 above ?

Arne
Jun 27 '08 #4


"Arne Vajhøj" wrote:
sippyuconn wrote:

"Arne Vajhøj" wrote:
sippyuconn wrote:
I wanted to store xml in a SQL Server Table

How does one go about it????

How does the Insert look like??? If I do it from a textfile or xmldocument?

How doe the Select look like if I want to put back into an xml document ??
Do you want to store one XML document in:
- multiple fields ?
- single field of type NTEXT ?
- single field of type XML ?
I have small xmlfiles and each row would be a different file

Is that 1, 2 or 3 above ?

Arne
I would say #2
Jun 27 '08 #5
sippyuconn wrote:
"Arne Vajhøj" wrote:
>sippyuconn wrote:
>>"Arne Vajhøj" wrote:
sippyuconn wrote:
I wanted to store xml in a SQL Server Table
>
How does one go about it????
>
How does the Insert look like??? If I do it from a textfile or xmldocument?
>
How doe the Select look like if I want to put back into an xml document ??
Do you want to store one XML document in:
- multiple fields ?
- single field of type NTEXT ?
- single field of type XML ?
I have small xmlfiles and each row would be a different file
Is that 1, 2 or 3 above ?

I would say #2
Then it is very simple using parameters.

Something like:

SqlCommand ins = new SqlCommand("INSERT INTO tbl VALUES(@id, @txt)", con);
ins.Parameters.Add("@id", SqlDbType.Int);
ins.Parameters.Add("@txt", SqlDbType.NText);
ins.Parameters["@id"].Value = id;
ins.Parameters["@txt"].Value = xmlstr;
ins.ExecuteNonQuery();

and:

SqlCommand sel = new SqlCommand("SELECT txt FROM tbl WHERE id = @id", con);
sel.Parameters.Add("@id", SqlDbType.Int);
sel.Parameters["@id"].Value = id;
SqlDataReader rdr = sel.ExecuteReader();
if(rdr.Read())
{
xmlstr = rdr.GetString(0);
}
else
{
// do something bad
}
rdr.Close();

Arne
Jun 27 '08 #6
Good morning, sippyuconn

I agree with Arne. When we use NTEXT field to store XML string, it has no
difference from storing a normal string. We can read the XML file into a
string with the code:

StreamReader reader = new StreamReader("path to the xml file");
string xml = reader.ReadToEnd();
reader.Close();

and insert it into a SQL server table with Arne's code snippet.

Please let us if you have any other concerns or questions.

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Jun 27 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Peter Afonin | last post by:
Hello, I'm creating something like a web site builder in ASP.Net, and I need to store an HTML code in the SQL server table. If my customer just enters an HTML code, it is stored in the SQL...
3
by: rinu911 | last post by:
hi all I am tring to insert a values to a table by checking if the value is there in the table already but it gives me this error "Query input must contain at least one table or query" ...
4
by: PALLAVI SHARMA | last post by:
I have made a web site using asp.net and c#. I used ms access for database, but i have problem that i don't know how to upload my ms access database on web server. I have domain name and password,...
9
by: shalskedar | last post by:
Access can store how many maximum number of records for a db.. Wanted to know what is the limitation of total number of records that Access db can store
8
by: ananthaisin | last post by:
How to reduce the table size for any table while using truncate or delete statements. In oracle 8i it was truncating the storage space but in 10g it is not .... I have given truncate statement in...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.