How to create n store data in Xmldatatype

[Mukesh]

Hi
all

I m Using SQL SERVER 2005

I have a requirement to store some data in xmldatatype using stored
procedure ,

Here is example

+++++++++
Database
=======================================================================
id(bigint) Name(NvarChar) Email(NvarChar) DetailXml(XML)
=======================================================================
1 Mukesh (e-mail address removed) null
2 Komal (e-mail address removed) null

=======================================================================
I have this type of data for Mukesh in stored procedure in database
server MSsql 2005 based server as input parameter

@id =1
@AddressType=Home
@Street1= Ground Floor
@Street2= C-81
@Street3= Sector19
@City = Noida
@DOb = 21-4-1982
@mobile = 919911847767

for storing this data in DetailXml (Datatype XML) xml schema i Have
creted to associate with the deatilsXml

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="test2" targetNamespace="http://tempuri.org/test2.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/test2.xsd"
xmlns:mstns="http://tempuri.org/test2.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="UserDetails">
<xs:complexType>
<xs:sequence>
<xs:element name="Address">
<xs:complexType>
<xs:sequence>
<xs:element name="Street1" type="xs:string" />
<xs:element name="Street2" type="xs:string" />
<xs:element name="Street3" type="xs:string" />
<xs:element name="City" type="xs:unsignedLong" />
</xs:sequence>
<xs:attribute name="Type" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="Dob" type="xs:date" />
<xs:element name="Mobile" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>


Please Help me how can i enter the data in Detailsxml column using the
only stored procedure and a schema written above suggest me if there is
any change or err in schema

My desired input in the detailsxml cell for user mukesh is

<UserDetails>
<Address type="Home">
<Street1>Ground Floor
</Street1>
<Street2>C-81
</Street2>
<Street3>Sector19
</Street3>
</Address>
<Dob>
21-4-1982
</Dob>
<Mobile>919911847767
</Mobile>
</UserDetails>

 

[Steven Cheng[MSFT]]

Hi Mukesh,

I've also found your another thread discussing on this issue in the
following newsgroup:

Subject: To insert dynamic value in Xml
Newsgroups: microsoft.public.dotnet.framework.aspnet

In that thread, I've provided some suggest on do the dynamic XML generation
in ASP.NET code logic and pass it into SQL statement or stored procedure.
However, based on your further description here, I get that you're going to
do the dynamic XML generation in SQL Server's stored procedure, correct?

If so, I think you'd better create a .NET based CLR stored procedure (SQL
Server 2005 specific feature) to accept the original parameters as below:

=========
@id =1
@AddressType=Home
@Street1= Ground Floor
@Street2= C-81
@Street3= Sector19
@City = Noida
@DOb = 21-4-1982
@mobile = 919911847767
=============

Then in the CLR stored procedure function(a .net class's static method),
you can use .NET XML api(under system.XML namespace) or just simple string
manipulation to construct the XML document you want. As I mentioned in
another thread, you can simply create a template with placeholder as below:

==========
<UserDetails>
<Address type="Home">
<Street1> {0}
</Street1>
<Street2> {1}
</Street2>
<Street3> {2}
</Street3>
</Address>
..........................
</UserDetails>

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

and then use string API to replace them with the input parameters. After
that you can use ADO.NET api to execute the actual underlying SQL update
XML statement.

here are some MSDN reference about how to create SQL 2005 CLR stoerd
procedure and how to processing XML in such code:

#How to: Create and Run a CLR SQL Server Stored Procedure
http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx


#Processing XML Showplans Using SQLCLR in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345130.aspx


Also, there're many reference about XML processing in .NET:


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


Hope this helps. Please feel free to post here if there is anything
unclear.

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]]

Hi Mukesh,

Still any questions on this issue? Please feel free to post here if there
is anything we can help.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


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