quotation marks in xml

I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.

this section shows the problem, i am loading xml from a file and trying to insert this into the sql database

XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));
reader.WhitespaceHandling = WhitespaceHandling.None;
XmlDocument xmlDocF = new XmlDocument();
xmlDocF.Load(reader);
this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;
this.SqlDataSource1.Update();
reader.Close();

pls help

Can you post some part of xml?

|||

<_n0011 HyperLink="undefined" Welcome="A'hneiv zipv meih" Language="undefined" country="undefined" x="1329" y="338"/>

this is an example.

there are about 400 similar tags with about 30 instances of the single quote mark. this may change in the future as i am trying to make the xml editable.

|||

I believe using Parameters will take care of the escaping automatically.

|||

I have tried the following syntax and got the same problems. I think this type of parameterising just parses the text into the string and produces effectively the same problem. Is there a different type of parameterising that I should be using.

this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] @.xmlDocF.InnerXml where [index] = 1" ;

I am a bit new to this subject and have looked for an article on this subject but not been able to find anything that deals with this specific requirement.

many thx for your assistance.

|||

what kind of parameter method did u have in mind?

|||

I created a DataSource with insert/update/delete commands and connected it to a GridView.

In edit mode, I entered this in the AVarCharField: Hello, "test" 'test'

When I clicked update, it worked.

The UpdateCommand looks like this:

UpdateCommand="UPDATE [TestTable] SET [CustomerName] = @.CustomerName, [Status] = @.Status, [AVarCharField] = @.AVarCharField WHERE [ID] = @.ID">

The Parameters look like this:

<UpdateParameters> <asp:Parameter Name="CustomerName" Type="String" /> <asp:Parameter Name="Status" Type="Single" /> <asp:Parameter Name="AVarCharField" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /></UpdateParameters>

I hope that helps.

Quick Import Wizard Question - Importing XML Data

Hello,

I want to import some XML data from the internet. For example, http://www.somexmldata.com/ returns some well formatted XML data that I want to import into SQL Server 2005.

If I create an Integration Services Project it's easy - I use an 'XML Source' and specify the URL as the 'XML location'. Job done!

However, I want to do it using the Data Import Wizard - how do I accomplish the same thing?

Many thanks,
BenUnfortunately, we were not able to add support for XML sources to the Import Wizard.

That is something that should be part of our upcoming work.

You are welcome to request that at the product feedback site.

Quick FOR XML EXPLICIT question - newbie

Hello.
I need to produce:
<AppForm>
<Forename Box =1>John</Forename>
<Surname Box=2>Smith</Surname>
</AppForm>
<AppForm>
<Forename Box =1>Peter</Forename>
<Surname Box=2>Jones</Surname>
</AppForm>
Where Box is like a box number on an application form and is the same
fixed value for every record.
In my SELECT Tag = 1, Parent = NULL,
I have
NULL AS [AppForm!4!Forename!element],
NULL AS [AppForm!4!Surname!element],
And later in SELECT Tag = 4, Parent = 1,
Forename AS [AppForm!4!Forename!element],
Surname AS [AppForm!4!Surname!element],
>From tbl... etc. etc.
Thank you if you can help me.
Since the name elements have attributes, they need to be treated as
so-called entity elements, meaning that they need their own level in an
explicit mode query. I give you an example below with the EXPLICIT mode and
one with the PATH mode available in SQL Server 2005.
create table customers(custid int, fname nvarchar(100), sname nvarchar(100))
go
insert into customers
select 100, N'John', N'Smith'
union
select 200, N'Peter', N'Jones'
go
select 1 as tag, NULL as parent,
custid as "AppForm!1!!hide",
NULL as "Forename!2!Box",
NULL as "Forename!2!",
NULL as "Surname!3!Box",
NULL as "Surname!3!"
from customers
union all
select 2, 1,
custid,
1, fname,
NULL, NULL
from customers
union all
select 3, 1,
custid,
NULL, NULL,
2, sname
from customers
order by "AppForm!1!!hide", tag
for xml explicit
go
select 1 as "Forename/@.Box", fname as "Forename/text()",
2 as "Surname/@.Box", sname as "Surname/text()"
from customers
for xml path('AppForm')
HTH
Michael
<mrjjohnson@.hotmail.com> wrote in message
news:1135770606.526554.327570@.g49g2000cwa.googlegr oups.com...
> Hello.
> I need to produce:
> <AppForm>
> <Forename Box =1>John</Forename>
> <Surname Box=2>Smith</Surname>
> </AppForm>
> <AppForm>
> <Forename Box =1>Peter</Forename>
> <Surname Box=2>Jones</Surname>
> </AppForm>
> Where Box is like a box number on an application form and is the same
> fixed value for every record.
> In my SELECT Tag = 1, Parent = NULL,
> I have
> NULL AS [AppForm!4!Forename!element],
> NULL AS [AppForm!4!Surname!element],
> And later in SELECT Tag = 4, Parent = 1,
> Forename AS [AppForm!4!Forename!element],
> Surname AS [AppForm!4!Surname!element],
> Thank you if you can help me.
>
|||Michael Rys [MSFT] wrote:
> Since the name elements have attributes, they need to be treated as
> so-called entity elements, meaning that they need their own level in an
> explicit mode query. I give you an example below with the EXPLICIT mode and
> one with the PATH mode available in SQL Server 2005.
>
<snip>
Thanks very much. I'll have a look at that.
Thanks again.

Quick FOR XML EXPLICIT question - newbie

Hello.
I need to produce:
<AppForm>
<Forename Box =1>John</Forename>
<Surname Box=2>Smith</Surname>
</AppForm>
<AppForm>
<Forename Box =1>Peter</Forename>
<Surname Box=2>Jones</Surname>
</AppForm>
Where Box is like a box number on an application form and is the same
fixed value for every record.
In my SELECT Tag = 1, Parent = NULL,
I have
NULL AS [AppForm!4!Forename!element],
NULL AS [AppForm!4!Surname!element],
And later in SELECT Tag = 4, Parent = 1,
Forename AS [AppForm!4!Forename!element],
Surname AS [AppForm!4!Surname!element],ed">
>From tbl... etc. etc.
Thank you if you can help me.Since the name elements have attributes, they need to be treated as
so-called entity elements, meaning that they need their own level in an
explicit mode query. I give you an example below with the EXPLICIT mode and
one with the PATH mode available in SQL Server 2005.
create table customers(custid int, fname nvarchar(100), sname nvarchar(100))
go
insert into customers
select 100, N'John', N'Smith'
union
select 200, N'Peter', N'Jones'
go
select 1 as tag, NULL as parent,
custid as "AppForm!1!!hide",
NULL as "Forename!2!Box",
NULL as "Forename!2!",
NULL as "Surname!3!Box",
NULL as "Surname!3!"
from customers
union all
select 2, 1,
custid,
1, fname,
NULL, NULL
from customers
union all
select 3, 1,
custid,
NULL, NULL,
2, sname
from customers
order by "AppForm!1!!hide", tag
for xml explicit
go
select 1 as "Forename/@.Box", fname as "Forename/text()",
2 as "Surname/@.Box", sname as "Surname/text()"
from customers
for xml path('AppForm')
HTH
Michael
<mrjjohnson@.hotmail.com> wrote in message
news:1135770606.526554.327570@.g49g2000cwa.googlegroups.com...
> Hello.
> I need to produce:
> <AppForm>
> <Forename Box =1>John</Forename>
> <Surname Box=2>Smith</Surname>
> </AppForm>
> <AppForm>
> <Forename Box =1>Peter</Forename>
> <Surname Box=2>Jones</Surname>
> </AppForm>
> Where Box is like a box number on an application form and is the same
> fixed value for every record.
> In my SELECT Tag = 1, Parent = NULL,
> I have
> NULL AS [AppForm!4!Forename!element],
> NULL AS [AppForm!4!Surname!element],
> And later in SELECT Tag = 4, Parent = 1,
> Forename AS [AppForm!4!Forename!element],
> Surname AS [AppForm!4!Surname!element],
> Thank you if you can help me.
>|||Michael Rys [MSFT] wrote:
> Since the name elements have attributes, they need to be treated as
> so-called entity elements, meaning that they need their own level in an
> explicit mode query. I give you an example below with the EXPLICIT mode an
d
> one with the PATH mode available in SQL Server 2005.
>
<snip>
Thanks very much. I'll have a look at that.
Thanks again.

queueing xml output to msmq

does anyone have an idea how can i queue xml output to msmq from the sql
query itselfSee KB article
http://support.microsoft.com/defaul...kb;en-us;555070
Sending a message to MSMQ from SQL requires writing a COM object and then
calling that COM object from a T-SQL procedure.
Mike
"kamal" <kamal@.discussions.microsoft.com> wrote in message
news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
> does anyone have an idea how can i queue xml output to msmq from the sql
> query itself
>|||I think in most cases you will find using an external application to
retrieve the XML and then send the MSMQ message will be more efficient
because the stored procedure will have to open the connection to MSMQ every
time it runs.
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
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> See KB article
> http://support.microsoft.com/defaul...kb;en-us;555070
> Sending a message to MSMQ from SQL requires writing a COM object and then
> calling that COM object from a T-SQL procedure.
> Mike
> "kamal" <kamal@.discussions.microsoft.com> wrote in message
> news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
>|||I've got a synchronization application (toned-down version of replication
with a few special requirements in it) that does that, but eliminates
polling by the application. I wrote an extended stored proc that sets a
named Windows event whenever another process needs to act. Code in a trigger
(in my case) calls the extended proc to set the event. The other process
waits on the named Windows event; when the event is fired, it queries the
database and does what it needs to do, which in this case actually happens
to be sending messages via MSMQ. You can then do all the performance
optimizations in the app, like Roger was saying, with the added benefit of
not having to poll. Of course, the drawback is that you are using a custom
extended procedure.
Mike
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eWex$gRVFHA.928@.TK2MSFTNGP15.phx.gbl...
> I think in most cases you will find using an external application to
> retrieve the XML and then send the MSMQ message will be more efficient
> because the stored procedure will have to open the connection to MSMQ
every
> time it runs.
> --
> 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
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
then
sql
>

queueing xml output to msmq

does anyone have an idea how can i queue xml output to msmq from the sql
query itself
See KB article
http://support.microsoft.com/default...b;en-us;555070
Sending a message to MSMQ from SQL requires writing a COM object and then
calling that COM object from a T-SQL procedure.
Mike
"kamal" <kamal@.discussions.microsoft.com> wrote in message
news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
> does anyone have an idea how can i queue xml output to msmq from the sql
> query itself
>
|||I think in most cases you will find using an external application to
retrieve the XML and then send the MSMQ message will be more efficient
because the stored procedure will have to open the connection to MSMQ every
time it runs.
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
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> See KB article
> http://support.microsoft.com/default...b;en-us;555070
> Sending a message to MSMQ from SQL requires writing a COM object and then
> calling that COM object from a T-SQL procedure.
> Mike
> "kamal" <kamal@.discussions.microsoft.com> wrote in message
> news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
>
|||I've got a synchronization application (toned-down version of replication
with a few special requirements in it) that does that, but eliminates
polling by the application. I wrote an extended stored proc that sets a
named Windows event whenever another process needs to act. Code in a trigger
(in my case) calls the extended proc to set the event. The other process
waits on the named Windows event; when the event is fired, it queries the
database and does what it needs to do, which in this case actually happens
to be sending messages via MSMQ. You can then do all the performance
optimizations in the app, like Roger was saying, with the added benefit of
not having to poll. Of course, the drawback is that you are using a custom
extended procedure.
Mike
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eWex$gRVFHA.928@.TK2MSFTNGP15.phx.gbl...
> I think in most cases you will find using an external application to
> retrieve the XML and then send the MSMQ message will be more efficient
> because the stored procedure will have to open the connection to MSMQ
every
> time it runs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
sql
>