replacing nodes and sub-trees

Hi,

I have an table where I use XML data type to store documents. The following is an example of original document:

<!ORIGINAL DOCUMENT>

<ABC>

<xyz>.....</xyz>

<xyz>.....</xyz>

<gef>.....</gef>

<qew>....</qew>

</ABC>

<!NEW DOCUMENT>

<ABC>

<xyz>.....</xyz>

<gef>.....</gef>

<reb>....</reb>

</ABC>

My question is how to update the original document so that the two previous instances of xyz tag are replaced by the new single xyz tag, the original gef tag is replaced by the new gef tag, and the reb tag is added to the original document as it is not not present in the original document. The qew will not be updated. I can do this using DOM but I am looking to accomplish this using xquery and xml dml. Is this possible ? I appreciate help on this.

Thanks

It sounds like your algorithm is

For each element name in the NEW document, delete all the elements of that name in the OLD document and insert the new element into the OLD document.

You could do this w/ XQuery DML by generating a DML statement based on the NEW document.

REPLACE Integers with Text Data

I am working with a database named “Documents” that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text

2 = Report

3 = Background

4 = Index

I would like to create a new column named “DocType” in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I).I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN “DocType” WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL

Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))

Go

But I have problems with the REPLACE method for replacing the numbers with letters.First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: “Incorrect syntax near 'REPLACE'.”

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: “Incorrect syntax near 'REPLACE'.”

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

Your UPDATE statement syntax is incorrect. And the 2nd syntax where you are trying to cast to DT_WSTR is not a valid SQL syntax also. See SQL Server Books Online for the complete UPDATE statement syntax and examples. UPDATE statement consists of SET, FROM and WHERE clauses. You are missing the SET clause. And you should use single-quotes preferably for string literals otherwise you will get errors most of the time depending on your SET options. Modify your update statement to:

UPDATE Fulldocuments SET DocType = REPLACE (DocType,'1','T')

But it doesn't seem like you want to create this DocType column in the first place. You should create a separate lookup table that contains the ids and description or type. You can then join with that table based on your ID value to get the description. This is much more flexible approach that doing it your way.

|||Thanks for correcting my syntax and the advice about creating a separate lookup table.