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 know which record is being updated in Update trigger?

Hi All,
Thanks in advance!
I want to generate an XML file in a Update trigger script.
Only the updated record needs to be exported to the XML file.
How do I know which record is being updated in the Update trigger?
Thank you so much!
Regards,
SeanSean,
Could be more than one record. Use the DELETED and INSERTED pseudo/logical
tables available within the firing of the trigger.
HTH
Jerry
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Define being updated. If you mean the rows that were touched, even if all
values remain the same:
select *
from inserted
If you mean those that have actually changed:
select *
from inserted
join deleted
on <some unchangable value, like a surrogate (identity)
key>
where inserted.col1 <> deleted.col2 --not nullable
or (inserted.col2 <> deleted.col2
or (inserted.col2 is null and deleted.col2 is not null)
or (inserted.col2 is not null and deleted.col2 is null) )
If you mean something else, then please provide more info.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Dear Louis,
I need all the records that have changed and inserted.
That means a modified record and new record.
For example:
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
I have a problem detecting the @.@.identity. I could not get the information
of which records have been modified and inserted.
Thank you very much!
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Define being updated. If you mean the rows that were touched, even if all
> values remain the same:
> select *
> from inserted
> If you mean those that have actually changed:
> select *
> from inserted
> join deleted
> on <some unchangable value, like a surrogate (identity)
> key>
> where inserted.col1 <> deleted.col2 --not nullable
> or (inserted.col2 <> deleted.col2
> or (inserted.col2 is null and deleted.col2 is not null)
> or (inserted.col2 is not null and deleted.col2 is null) )
> If you mean something else, then please provide more info.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
>|||Yeah, you cannot rely on identities to identify a row like this. You really
need to have another key that you can expect not to change. Since you
insert the values, you know them and can trust them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
> Dear Louis,
> I need all the records that have changed and inserted.
> That means a modified record and new record.
> For example:
> CREATE TRIGGER trgUpdate
> ON User_Master
> FOR UPDATE
> AS
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
> REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
> SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
> CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
> EXEC master..xp_cmdshell @.bcpCommand
> I have a problem detecting the @.@.identity. I could not get the information
> of which records have been modified and inserted.
> Thank you very much!
>
> Regards,
> Sean
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Dear Louis,
Thanks for you information.
It helps a lot!
I am trying to copy the modified or new data from User_Master into a
User_MasterTemp table using an Update trigger.
CREATE TRIGGER trgUpdate ON User_Master
FOR UPDATE
AS
Delete From User_MasterTemp
Insert into User_MasterTemp Select * From Inserted
GO
Then do a exporting to XML file from the temp table using an Insert trigger.
CREATE TRIGGER User_MasterInsert ON [dbo].[User_MasterTemp]
FOR INSERT
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_MasterTemp for xml raw"
queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
Hopefully, the code can work. I am doing the troubleshooting cause the
script in the Insert trigger hangs the SQL Server.
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O04yriG0FHA.916@.TK2MSFTNGP10.phx.gbl...
> Yeah, you cannot rely on identities to identify a row like this. You
> really need to have another key that you can expect not to change. Since
> you insert the values, you know them and can trust them.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
>

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

How do i getting many Rows from a single XML column?

using SQL 2005...

I have a table called training. It has 2 columns ID and data.
The ID column is a INT.
The data column is an XML data type that contains XML looking like the following:
<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005<ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>

if we asume that the table has 3 rows with the ID field being 1-3 and all the XML is the same as above.
I'm trying create an SQL statement that outputs the following results

id	ProgramName	UrgencyLevel
1	Learn SQL 2005	3
1	Learn To Walk	1
1	Learn To Eat	1
2	Learn SQL 2005	3
2	Learn To Walk	1
2	Learn To Eat	1
3	Learn SQL 2005	3
3	Learn To Walk	1
3	Learn To Eat	1

please can some one help me?

You will need to use the nodes() to break your xml into multiple rows then CROSS APPLY to that. For example:

Dan

CREATE TABLE #t1(
id int,
data xml
)

INSERT INTO #t1 SELECT 1, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

SELECT ID, t.c.value('ProgramName[1]', 'NVARCHAR(MAX)') + ' ' + t.c.value('UrgencyLevel[1]', 'NVARCHAR(MAX)')
FROM #t1
CROSS APPLY
#t1.data.nodes('TrainingRequests/TrainingRequest') AS t(c)

|||Thank you, you saved me ripping out the rest of my hair!!!