I have attached the xml.

The following statement doesn't work either. It still returns '1 1'

select

xmlData.query(for $s in (test/violation)

where data($s/Code) = "Minor"

...

It is up to the user to deploy them in a database or a filegroup.

Its easier to select the data that needs to be inserted first, so in your example it can be something like:

insert into test(val,val2)

( select

CASE WHEN color = 'K'...

I don't know if this is what you were looking for but to add another column to the update statement, you will do something like :

UPDATE test

SET ...