Solved

How to add extra Element to an existing XML Schema Collection

Posted on 2010-09-12
2
364 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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

717 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