Solved

How to add extra Element to an existing XML Schema Collection

Posted on 2010-09-12
2
360 Views
Last Modified: 2012-05-10
Hi,
I have got a table with several hundred thousand records and one column with a typed XML data type.  My question is how do I go about adding a new element to an existing XML Schema collection? and how do I up values for this new added element?. The scripts below are used to create the table and the XML Schema Collection:
CREATE TABLE Customer
(CustomerId INT, Profile  XML (CustomerProfileSchemaCollection))
GO

CREATE XML SCHEMA COLLECTION CustomerProfileSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified"
   attributeFormDefault="unqualified"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" > 
    <xsd:element name="Customer Details">
                        <xsd:complexType mixed="false">
                                    <xsd:sequence>
                                                <xsd:element name="FirstName" type="xsd:string"/>
                                                <xsd:element name="Surname" type="xsd:string"/>
                                                <xsd:element name="Home Phone" type="xsd:string"/>
                                    </xsd:sequence>                      
                        </xsd:complexType>
    </xsd:element>
</xsd:schema>';  

I want to add a new element called "Mobile Phone" to the above XML Schema Collection and want to be able to update new values based on CustomerId. Can some one guide me through on how to achieve this?

Many thanks in advance.

CL
0
Comment
Question by:Programmers
2 Comments
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 500 total points
ID: 33659899
http://www.extremeexperts.com/SQL/Yukon/ChangingXMLSchema.aspx
I hope above link explain what you need.
 
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37155796
Just add another line for the new element in schema above below HomePhone as:

<xsd:element name="Mobile Phone" type="xsd:string"/>

and you could write your own T-SQL stored proc to read the XML in SQL temp table then do that update like in pseudo code below: (just change ContactId to CustomerId)
 
CREATE PROCEDURE dbo.my_XML_update
	@XMLData NTEXT,
	@myId SMALLINT
AS
SET NOCOUNT ON

-- Retrieve XML data
DECLARE @hocDoc INT

DECLARE @Comments NVARCHAR(2048)

EXEC SP_XML_PREPAREDOCUMENT @hocDoc OUTPUT, @XMLData 

-- OPEN XML 
-- Param1 is Pointer to DOC
-- Param2 is XML doc hiearchy
-- Param3 is Use Element Structure rather than Attributes


SELECT
	ContactId,
	ContactName,
	HomePhone,
	MobilePhone
INTO 
	#tmpContact
FROM 
	OPENXML(@hocDoc, '/root/Contact', 2) 
WITH (ContactId  INTEGER, ContactName NVARCHAR(50),HomePhone NVARCHAR(20),MobilePhone NVARCHAR(20))

--this is how you populate the contact xml
SELECT	Contact.ContactId,
		ISNULL(Contact.ContactName, '') AS ContactName, 
		ISNULL(Contact.HomePhone, '') AS HomePhone, 
		ISNULL(Contact.MobilePhone, '') AS MobilePhone
FROM #tmpContact Contact, Contact C
WHERE	Contact.ContactId = C.Id
ORDER BY	Contact.ContactName
FOR XML AUTO, ELEMENTS

-- Then you can do an INSERT
INSERT INTO Contact (Id, ContactName)
	SELECT ContactId,ContactName,HomePhone,MobilePhone 
	FROM #tmpContact 

--or an UPDATE 
UPDATE Contact SET ContactName = t.ContactName,
					HomePhone = t.HomePhone,
					MobilePhone = t.MobilePhone
FROM #tmpContact t
			WHERE	contact.Id = #tmpContact.ContactId 
		
-- Drop the temp Table
DROP TABLE #tmpContact

-- and MUST release XML doc to avoid memeory issues
EXEC SP_XML_REMOVEDOCUMENT @hocDoc
GO

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 36
Query - which index being used? 2 48
Sql Query join multiple table and distinct records 7 29
Error when saving to sql table a '/' 5 25
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now