Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Question Unanswered: Requesting some help with XQuery to update xmldata

    Hello All:




    I have a table T1 with an xml column XMLData. The table and XMLdata value is as below

    Create table T1
    (
    XMLData xml
    )
    <Root>
    <Files>
    <File ID="10" Name = "MyFileName 1">
    <FileRevisions>
    <FileRevision ID = "1" Revision="1.0.0.0"/>
    <FileRevision ID = "2" Revision="1.0.0.1"/>
    <FileRevision ID = "3" Revision="1.0.0.2"/>
    </FileRevisions>
    </File>
    <File ID="20" Name = "MyFileName 2">
    <FileRevisions>
    <FileRevision ID = "10" Revision="2.0.0.0"/>
    <FileRevision ID = "20" Revision="2.0.0.1"/>
    <FileRevision ID = "30" Revision="2.0.0.2"/>
    </FileRevisions>
    </File>
    </Files>
    </Root>

    Table T2 stores current file versions. The structure and sample data are
    Create Table T2
    (
    [FileName] Nvarchar(20),
    [Version] Nvarchar(10)
    )

    INSERT INTO T2([FileName],[Version]) VALUES ('MyFileName 2', '2.0.0.3')
    INSERT INTO T2([FileName],[Version]) VALUES ('MyFileName 3', '3.0.0.0')

    My requirement is to edit the xml as per the conditions below.

    1. Read Table T2. If the filename is in T1.XmlData, add the fileversion as the last node under the respective file node in xml.
    2. If table name is not in T1.xmlData, add the file node as well as the file version node in xml.

    so after the xml update, the xml data should become

    <Root>
    <Files>
    <File Name = "MyFileName 1">
    <FileRevisions>
    <FileRevision Revision="1.0.0.0"/>
    <FileRevision Revision="1.0.0.1"/>
    <FileRevision Revision="1.0.0.2"/>
    </FileRevisions>
    </File>
    <File Name = "MyFileName 2">
    <FileRevisions>
    <FileRevision Revision="2.0.0.0"/>
    <FileRevision Revision="2.0.0.1"/>
    <FileRevision Revision="2.0.0.2"/>
    <FileRevision Revision="2.0.0.3"/>
    </FileRevisions>
    </File>
    <File Name = "MyFileName 3">
    <FileRevisions>
    <FileRevision Revision="3.0.0.0"/>
    </FileRevisions>
    </File>
    </Files>
    </Root>

    I did several attempts to achieve this by looking at various code samples in internet, but did not succeed.

    Kindly advice how to achieve this.

    Thanks,
    Baburaj
    Cheers....

    baburajv

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Do you have to do an actual xml update? The few times I dealt with xml in SQL, I ended up forming (or re-forming) the xml that I need outside of the table, and then doing the update to the xml column with the final content of the field.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •