New answers tagged xml
Here it is WITH x AS (SELECT DBMS_XMLGEN.CONVERT(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&\1'), 1 ) AS message FROM dual) SELECT message, UTL_I18N.UNESCAPE_REFERENCE(message) conv_message from x;
Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially: UPDATE existing rows INSERT missing rows You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted. Beyond that there are different ways to ...
The .nodes() method must start at the root of your xml. It will then return one "row" per matching node for the SELECT clause to work on. It should look like this: from @notesToUpsert.nodes('/Notes/Note') as note(col) (BTW using "note" as an alias is confusing. Perhaps T(c) would be easier to debug?) Then the XQuery in the SELECT clause operates in the ...
Top 50 recent answers are included