title: Update XMLTYPE in Oracle table with values from it's fields
body: <p>I have an existing table in an Oracle 10gR2 that I added a <code>XMLTYPE</code> column to it. Now, I need to fill this field with a XML that is composed with a mixture of values from the fields of each row, but I can't get the syntax down correctly. Below is the correctly working SELECT statement that generates the desired XML for each row:</p>
  
  <pre><code>SELECT XMLELEMENT("TBLOrigs",
    XMLELEMENT("TBLOrig", XMLATTRIBUTES(Field1 AS "Field1"),
    XMLFOREST(Field2 AS "Field2", Field3 AS "Field3", Field4 AS "Field4", Field5 AS "Field5"),
      XMLELEMENT("Dets",
      XMLFOREST(Field6, Field7 AS "Field7")))) AS XmlCol
    FROM TBLOrigin Orig1
  WHERE Field1 &lt;= 10
  </code></pre>
  
  <p>The UPDATE I'm trying is something like this, but it's not working:</p>
  
  <pre><code>UPDATE TBLOrigin
  SET AUXFIELD = (
    SELECT XMLELEMENT("TBLOrigs",
    XMLELEMENT("TBLOrig", XMLATTRIBUTES(Field1 AS "Field1"),
    XMLFOREST(Field2 AS "Field2", Field3 AS "Field3", Field4 AS "Field4", Field5 AS "Field5"),
      XMLELEMENT("Dets",
      XMLFOREST(Field6, Field7 AS "Field7")))) AS XmlCol
    FROM TBLOrigin Orig1
    WHERE Orig1.Field1 = Orig2.Field1
    AND Orig1.Field2 = Orig2.Field2
    AND Orig1.Field3 = Orig2.Field3
    AND Orig1.Field4 = Orig2.Field4
    AND Orig1.Field5 = Orig2.Field5)
  FROM TBLOrigin Orig2
  WHERE Field1 &lt;= 10
  </code></pre>
  
  <p>I'm trying to UPDATE each row with the XML content generated from it's fields. I keep getting the error below:</p>
  
  <pre><code>Erro na Linha de Comando:13 Coluna:47
  Relatório de Erro:
  Erro de SQL: ORA-00933: SQL command not properly ended
  00933. 00000 -  "SQL command not properly ended"
  *Cause:    
  *Action:
  </code></pre>
  
  <p>Just as a reference, below is the working UPDATE in Transact Sql</p>
  
  <pre><code>UPDATE dbo.TBLOrigin
  SET AuxInfo = (SELECT 
    Field1 as "@Field1",
    Field2 as "Field2", 
    Field3 as "Field3", 
    Field4 as "Field4", 
    Field5 as "Field5", 
    Field6 as "Dets/Field6", 
    Field7 as "Dets/Field7"
  FROM dbo.TBLOrigin AS Orig1
  WHERE Orig1.Field1 = Orig2.Field1
  AND Orig1.Field2 = Orig2.Field2
  AND Orig1.Field3 = Orig2.Field3
  AND Orig1.Field4 = Orig2.Field4
  AND Orig1.Field5 = Orig2.Field5
    FOR XML PATH('TBLOrig'), TYPE, ROOT('TBLOrigs'))
  FROM dbo.TBLOrigin Orig2
  </code></pre>
  
  <p>Tks so much for any help</p>