473,238 Members | 1,591 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

How to create n store data in Xmldatatype

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 mu****@abc.com null
2 Komal Ko***@abc.com 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>



Nov 25 '06 #1
2 1696
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/lib...1z(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/Buildi...k/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/subscripti...ult.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/subscripti...t/default.aspx.

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

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


Nov 27 '06 #2
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.

Nov 30 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: davidw | last post by:
As I asked in last post, I want to put some logic in a object and all my webcontrol instance will access that object, the object is responsed to retrieve data from database if the data has not been...
2
by: mpriem | last post by:
Hi, I am trying to create a webapplication which reads a Xml Document and dynamically creates tables containing the values. The Xml Document is created by a windows service I programmed. So if I...
11
by: mesut demir | last post by:
Hi All, When I create fields (in files) I need assign a data type like char, varchar, money etc. I have some questions about the data types when you create fields in a file. What is the...
4
by: sandeep | last post by:
When we use STL which memory space it will use whither it is stack or heap or data segment How to make STL to create in heap? How to make whole container to create in heap? I think container...
1
by: Dave | last post by:
I have multiple forms that will create an object. Basically a energy efficiency measure object. The measure object will have a couple of required properties set but after that it can have 10-20...
11
by: memeticvirus | last post by:
I have an array cli::array<float, 2and I would like to access a subset of it's values by compiling an array of pointers. But, it's not possible to create an array of type...
25
by: Licheng Fang | last post by:
I mean, all the class instances that equal to each other should be reduced into only one instance, which means for instances of this class there's no difference between a is b and a==b. Thank...
0
by: ramuygl | last post by:
want to create store procedure that. want to send the table name as argument and retrive the data of that argument. and want to store data in temperary table using the insert query. HERE I AM...
15
by: lxyone | last post by:
Using a flat file containing table names, fields, values whats the best way of creating html pages? I want control over the html pages ie 1. layout 2. what data to show 3. what controls to...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.