Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

updating xml schema collections Expand / Collapse
Posted Tuesday, May 5, 2009 9:49 AM


Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 457, Visits: 681
I am not sure how to update existing data in an xml field when we add elements and remove them. for example
<xsd:schema xmlns:xsd="">
<xsd:element name="XXXX">
<xsd:restriction base="xsd:anyType">
<xsd:element name="Reference" type="xsd:string" />
<xsd:element name="Description" type="xsd:string" />
<xsd:element name="Family" type="xsd:string" />
<xsd:element name="Type" type="xsd:string" />
<xsd:element name="PT" type="xsd:string" />

We need to remove "PT" from the schema. there is existing data out there that needs to be updated. How do I update the xml field to remove that portion. also say in the future we add an element how do I add the element?
Post #710402
Posted Tuesday, May 5, 2009 12:07 PM



Group: General Forum Members
Last Login: Wednesday, February 10, 2016 11:50 AM
Points: 6,897, Visits: 13,559
As per BOL as well as Jacob Sebastians great Book "The Art of XSD" there is no way to remove an element from a stored schema using T-SQL. The only way to alter the specific schema is to drop it an re-create it without the element in question. For details please see BOL, section "DDL for Managing XML Schema Collections in the Database".

To add/modify/remove elements/attributes in question from existing XML data, please see BOL under "XML Data Modification Language (XML DML)".

If you have trouble modifying the sample code to meet your requirements please show what you've done and what you're struggling with.

A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #710525
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse