?
Solved

How to add extra Element to an existing XML Schema Collection

Posted on 2010-09-12
2
Medium Priority
?
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

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

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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