By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,916 Members | 1,306 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,916 IT Pros & Developers. It's quick & easy.

Store XML in SQL Server Table

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a


"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

P: n/a
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

P: n/a


"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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.