How to add extra Element to an existing XML Schema Collection

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
ProgrammersAsked:
Who is Participating?
 
Easwaran ParamasivamConnect With a Mentor Commented:
http://www.extremeexperts.com/SQL/Yukon/ChangingXMLSchema.aspx
I hope above link explain what you need.
 
0
 
lcohanDatabase AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.