To insert dynamic value in Xml

[Mukesh]

sir,
i am developing a database, which will store the users profile
both personal and professional
which includes the address, telephone, gender and etc.
in my main table i have created a column of xml data type. i was
successful in inserting the static
value inside my table.by following code:---


UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher>
before (/book/title)[1]')



since the profile of users vary from person to person, i cant insert
static value. i need to generate dynamic value.
the value which is inserted by user through text box should get inserted
in my main table .


kindly guide me.

 

[Han]

Hello

Your question is a bit specific for this NG. From now on, use
microsoft.public.sqlserver.xml.

You seem to want to use sql variable within your XML DML. Right? Then,

Try

UPDATE docs
SET xCol.modify ('
insert <publisher>{sql:variable("@myvariable")}</publisher>
before (/book/title)[1]')

Instead of,

UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher>
before (/book/title)[1]')

sir,
i am developing a database, which will store the users profile both
personal and professional
which includes the address, telephone, gender and etc.
in my main table i have created a column of xml data type. i was
successful in inserting the static
value inside my table.by following code:---


UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher>
before (/book/title)[1]')



since the profile of users vary from person to person, i cant insert
static value. i need to generate dynamic value.
the value which is inserted by user through text box should get inserted
in my main table .


kindly guide me.

 

[Steven Cheng[MSFT]]

Thanks for Han's input.

Hi Mukesh,

I'm wondering whether you're going to do the dynamic update XML at SQL
Server side (as Han has suggested) or in ASP.NET/.NET code logic? Based on
my understanding, since the udpate xml content may depend on the business
logic in application layer, it is proper to dynamically generate the XML
based SQL update statement in .NET/ASP.NET code logic.

Would you provide me some more examples of the possible update statements
that may be used according to different user profiles(like the below one)

=========
UPDATE docs
SET xCol.modify ('
insert <publisher>Microsoft Press</publisher>
before (/book/title)[1]')
===========

Generally, if we do the dynamic update statement generation in .NET code,
the idea is as below:

1. define a update statement template with a placeholder section (for the
dynamic generated XML content)

2. generate the dynamic XML update string and insert into the update
statement template in #1

3. execute the whole XML based upate statement

Also, if the XML string is very complex, you can consider use the
Sysetm.Xml classes (such as XmlDocument class) to help generate it.

#XML in the .NET Framework
http://msdn.microsoft.com/XML/BuildingXML/XMLinNETFramework/default.aspx

BTW, you mentioned that you'll let client user input the update content
through Textbox on page, correct? If so, in ASP.NET, the page will by
default validate any markup code in the postback data(and throw validation
exception). Therefore, if you want to allow client use input markup data
(like <data>....</data>), you need to turn off the request validation on
the certain page like:

<%@ Page Language="C#" ValidateRequest="false" %>


#How To: Prevent Cross-Site Scripting in ASP.NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html
/paght000004.asp


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



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

 

[Steven Cheng[MSFT]]

Hello Mukesh,

Have you got any further progress or ideas on this issue or does the
information in the previous messages helps a little? Please feel free to
post here if there is anything else we can help.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


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