Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

updating xml schema collections Expand / Collapse
Author
Message
Posted Tuesday, May 05, 2009 9:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:32 AM
Points: 415, Visits: 595
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="http://www.w3.org/2001/XMLSchema">
<xsd:element name="XXXX">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<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" />
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>

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?
thanks
Post #710402
Posted Tuesday, May 05, 2009 12:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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.




Lutz
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