Solved

How to add extra Element to an existing XML Schema Collection

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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