How do i make a autonumber field in my table!

Hi
im new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql.

Ive tried setting my primary key field touniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!

hope someone can help!
thanks
SQL Server uses IDENTITY for the auto number, in SQL Server it is a property to the column it is not a column, there are many IDENTITY in SQL Server what you need is the property. The Uniqueidentifier is different that uses a GUID which is a 16bytes Binary data type in SQL Server while IDENTITY is INT. Run a search for SET IDENTITY property in SQL Server BOL (books online). Hope this helps.|||thanks didnt know it was that easy!

How do i load SQL Server XML datatype into XmlDocument in C#

Hi there,

How do i load a XML document saved in SQL server as a XML data type into

XmlDocument xdoc =newXmlDocument();
xdoc.Load(// INTO HERE);

I can load a xml file saved to disk but haven't figured out how to retrive from a recordset. I have tried the following:

XmlDocument xdoc =newXmlDocument();
xdoc.Load(GetXmlFile(pnrID).ToString());

publicstring GetXmlFile(int pnrID){

SqlConnection cnn =null;
SqlCommand cmd =null;

string XML ="";

try{

cnn =newSqlConnection();
cnn.ConnectionString ="Server=;Initial Catalog=;UID=;PWD=;";
cnn.Open();

string selectQry ="SELECT [XML] FROM [TEMP_PNR] WHERE PnrID = @.PnrID";

cmd =newSqlCommand(selectQry, cnn);

cmd.Parameters.AddWithValue(

"@.pnrID", pnrID); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read())
XML = rdr.GetSqlXml(0).ToString();

}

catch (Exception ex)
{
throw ex;
} finally{
cmd.Dispose();
cnn.Close();
}return XML;

}

But this genereates the following error:Could not find file 'C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlTypes.SqlXml'.

Any idea how i can achive this?

If you want to load the XmlDocument from a string, then use the LoadXml method (i.e. xdoc.LoadXml(your_xml_string_here)).|||

Hi Vito, I did try LoadXml but it did not work.

The trick was rdr.GetSqlXml(0).CreateReader()); instead of rdr.GetSqlXml(0).ToString());

Below is my solution, if anyone have a better way of achiving this please let me know!

string selectQry ="SELECT [XML] FROM [TEMP_PNR] WHERE [PK_PnrID] = @.pnrID";
cmd =newSqlCommand(selectQry, cnn);
cmd.Parameters.AddWithValue("@.pnrID", pnrID);

XmlDocument xdoc =newXmlDocument();
SqlDataReader rdr = cmd.ExecuteReader();

if (rdr.Read())
xdoc.Load(rdr.GetSqlXml(0).CreateReader());

|||

I found instructions from Wrox bookProfessional ASP.NET 2.0 XMLISBN:0764596772 how to load XML data from SQL Server 2005 into aXmlDocument

Instead of
SqlDataReader rdr = cmd.ExecuteReader();

Do
XmlReader rdr = cmd.ExecuteXmlReader();

Instead of
if (rdr.Read())
xdoc.Load(rdr.GetSqlXml(0).CreateReader());

Do
if (rdr.Read())
xdoc.Load(rdr);

Hope this helps any other developers out there!

|||

This is the working code if anyone needs a template.

XmlDocument xdoc =newXmlDocument();
SqlConnection cnn =null;
SqlCommand cmd =null;

try
{
cnn =newSqlConnection();
cnn.ConnectionString ="xxxxxxxxxxxxxxxxx";
cnn.Open(); string selectQry ="SELECT [Xml] FROM [Table1] WHERE [PK_ID] = @.ID";
cmd =newSqlCommand(selectQry, cnn);
cmd.Parameters.AddWithValue("@.ID", ID); XmlReader reader = cmd.ExecuteXmlReader(); if (reader.Read())
xdoc.Load(reader);
}catch (Exception ex)
{
throw ex;
}finally
{
cmd.Dispose();
cnn.Close();
}

How do I insert text into empty xml element in sql server 2005?

Hello

I have the following xml data stored in an xml datatype colmun in sql
server 2005:

<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>

I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.

...
<pagetext>
Some content goes here.
</pagetext>
...

I've tried to achieve this using the query below:

update tbl_tree SET theTree.modify(' replace value of (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')

but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'

I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.

Is there a way to do this?

Any help very much appreciated.

For those interested, this has now been answered via a newsgroup here:
http://groups.google.com/group/microsoft.public.sqlserver.xml/browse_thread/thread/424e62d11c396b3e/bab10449576dccd8#bab10449576dccd8

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
PeterYou would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegroups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>|||Thanks Michael - I appreciate your help.sql

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
Peter
You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegro ups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>
|||Thanks Michael - I appreciate your help.

How Do i insert For XML Result in another table

Hi,
I have table with nText field and i want to insert 'for xml query' into that field, what is datatype for 'for xml quey result'
e.g (select * from publishers for XML auto elements)

JigarHi Jigar,

I'm not sure I understand. Do you mean that you want to save the results of that SELECT query, which returns XML, in the nText field? If so, nText is fine, since the data is text.

Or do you mean something else?

Don|||I have table with fields like
itemID int,
XML NText

is there any way i can insert through StoredProcedure .

my storedprocedure should do like this
--gets data from first table using for XML
--Insert XML data into another table as described above.

i can not find any way to handle this through storedprocedure

currently i am doing following using client

-- open datareader that featches xml from first table
-- save xml using another call to database.

i was wondering if there is any way to do that using same sproc.|||Well, I've been mulling this over without much of a solution. You could always use a cursor but that would be ugly, fragile, and slow.

I'll keep thinking about this. I'm sure I'm missing something simple.

Anyone else?

Don