How to compare schemas and update data


is there way can compare 2 schema's , apply changes in xml data.

for example: 

 

i have table untyped xml column

·         table dbo.contacts(           

            contact_id                 int                     not null         primary key,

            schema_name          varchar(100)        null,

            attributes_xml            xml                    null   

)

i have multiple schemas in sys.xml_schema_collections.

one of them contactsschema

<xs:schema id="contactsschema"

    targetnamespace="http://tempuri.org/contactsschema.xsd"

    elementformdefault="qualified"

    xmlns="http://tempuri.org/contactsschema.xsd"

    xmlns:mstns="http://tempuri.org/contactsschema.xsd"

    xmlns:xs="http://www.w3.org/2001/xmlschema">

  <xs:complextype name="contact">

    <xs:sequence>

      <xs:element name="fname" type="xs:string" />

<xs:element name="lname" type="xs:string" />

      <xs:element name="age" type="xs:int"   />  

    </xs:sequence>

  </xs:complextype>

  <xs:element name="contacts">

    <xs:complextype >

      <xs:sequence minoccurs="0" maxoccurs="unbounded">

        <xs:element name="contact" type="contact" />

      </xs:sequence>

    </xs:complextype>

  </xs:element>

</xs:schema>

records added dbo. contacts table schemaname = ‘contactsschema’ , the related xml data  (while adding records validate xml dynamically: please see post: ( http://social.msdn.microsoft.com/forums/en-us/sqlxml/thread/1b122a53-7c96-447d-886e-55ac5ea4da53 ).

now schema changed.

 

contactsschema (after change):

 

<xs:schema id="contactsschema"

    targetnamespace="http://tempuri.org/contactsschema.xsd"

    elementformdefault="qualified"

    xmlns="http://tempuri.org/contactsschema.xsd"

    xmlns:mstns="http://tempuri.org/contactsschema.xsd"

    xmlns:xs="http://www.w3.org/2001/xmlschema">

  <xs:complextype name="contact">

    <xs:sequence>

      <xs:element name="firstname" type="xs:string" />

<xs:element name="lastname" type="xs:string" />

      <xs:element name="age" type="xs:int"   />

      <xs:element name="ssn" type="xs:string"   />

    </xs:sequence>

  </xs:complextype>

  <xs:element name="contacts">

    <xs:complextype >

      <xs:sequence minoccurs="0" maxoccurs="unbounded">

        <xs:element name="contact" type="contact" />

      </xs:sequence>

    </xs:complextype>

  </xs:element>

</xs:schema>

i drop , create contactsschema in xml schema collections.

how update xml data in contacts table schema: contactsschema.

is there better way compare old , new schema  , update xml data.


thank , appreciate help.

i dont think there easy way this. comparing schema not easy task in tsql. further, updating xml data based on schema makes complecated task. while doable bunch of complex tsql code, suggest explore possibilities of getting done may in .net. 

even in .net, comparing schema , re-formatting xml document might not easy. if can build xslt style sheet transform old data new structure, might easier. 


http://jacobsebastian.blogspot.com/


SQL Server  >  SQL Server XML



Comments

Post a Comment

Popular posts from this blog

Reindexing

hi     using sql server 2005 ee sp4. our database size 150 gb. weekly once schedule reindex job. ran 4 5 hours maximum. but recent days getting more 13 hours. the possibilities there? ragu thangavel how perform reindex? maint plan? or check fragmentation first , reorganize fragmented? if later, quite more data nowadays fragmented before. if not, consider doing "smart" defrag (ola.hallengren.com). tibor karaszi, sql server mvp | web | blog SQL Server  >  SQL Server Database Engine
Read more

Users List

hello everyone, i"m builing tool tfs , want user list form tfs "assigned to" field. if 1 can post code review i"ll gled.   thanks hello, to imports users tfs, can try this: using microsoft.teamfoundation.server; using microsoft.teamfoundation.client; using microsoft.teamfoundation.workitemtracking.client; icredentialsprovider provider = new uicredentialsprovider (); teamfoundationserver tfs = teamfoundationserverfactory .getserver(“your_server_name”, provider); tfs.authenticate(); igroupsecurityservice gss = ( igroupsecurityservice )tfs.getservice( typeof ( igroupsecurityservice )); identity idsid = gss.readidentity( searchfactor .accountname, "team foundation valid users" , querymembership .expanded); identity [] idusername = gss.readidentities( searchfactor .sid, idsid.members, querymembership .none); foreach ( identity id in idusername) {       if (id != null && id.type == identitytype .windowsuser) ...
Read more

List of charts

Image
i'm trying build report show activity for reservations placed and satisfied month branch. the report has parameter the item category and dataset has fields month category branch reservations placed reservations satisfied on front sheet i've created system wide figures 2 columns per month and a matrix broken down category (e.g. child fiction, child non fiction, adult fiction, adult non fiction) has row , column totals. i want have page each branch in system. what's best way of approaching this?  add parent group based on branch field in row groups  and add page breaks repeat matrix report every branch. please mark answer if post solved problem. milan das SQL Server  >  SQL Server Reporting Services, Power View ...
Read more