I would like to replace a element with a new xml element in a Open XML document using XQuery

Question

Here is a sample of the XML document before I run my XQueries:

<?xml version="1.0" encoding="UTF-8" standalone='yes'?>
<w:document xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office"    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"   xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
  <w:body>
   ...
 <w:p w:rsidR="00BB265E" w:rsidRDefault="00BB265E">
     <w:pPr>
    <w:pStyle w:val="DefaultText"/>
    <w:ind w:left="720" w:hanging="720"/>
  </w:pPr>
  <w:r>
    <w:t>1.7</w:t>
  </w:r>
  <w:r>
    **<w:tab/>**
  </w:r>
  <w:r w:rsidRPr="001C1D1B">
    <w:rPr>
      <w:shd w:val="clear" w:color="auto" w:fill="FABF8F"/>
    </w:rPr>
    <w:t>Member means any person who is enrolled in</w:t>
  </w:r>
 ...

Here is some of the XQueries I tried to run:

Query 1

UPDATE XML_TORTEST
  SET XMLDOC =  updateXML(XMLDOC,'declare namespace w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"; /w:document/w:body/w:p/w:r/w:tab',
                     XMLType('<w:t> </w:t>'))
  WHERE XML_TORTEST_ID = 1
 AND XMLExists('$p/w:document/w:body/w:p/w:r/w:tab'
                  PASSING XMLDOC AS "p");

This gives this error:

SQL Error: ORA-31013: Invalid XPATH expression 31013. 00000 - "Invalid XPATH expression" *Cause: XPATH expression passed to the function is invalid. *Action: Check the xpath expression for possible syntax errors.

Query 2 - Works, but XML output is not correct (not literally)

 UPDATE XML_TORTEST po
  SET po.XMLDOC =
    XMLQuery('declare namespace  w="http://schemas.openxmlformats.org/wordprocessingml/2006/main";     
              copy $i := $p1
              modify
              (
             for $j in $i/w:document/w:body/w:p/w:r/w:tab
             let $newn := ''<w:t> </w:t>''
             return replace node $j with $newn)
             return $i' PASSING po.XMLDOC AS "p1"
          RETURNING CONTENT)
 WHERE XML_TORTEST_ID = 1
 AND XMLExists('declare namespace w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"; $p/w:document/w:body/w:p/w:r/w:tab'
              PASSING po.XMLDOC AS "p");

Here is the sample XML after I ran Query 2:

 <w:p w:rsidR="00BB265E" w:rsidRDefault="00BB265E">
      <w:pPr>
        <w:pStyle w:val="DefaultText"/>
        <w:ind w:left="720" w:hanging="720"/>
      </w:pPr>
      <w:r>
      <w:t>1.7</w:t>
  </w:r>
    **<w:r>&lt;w:t&gt; &lt;/w:t&gt;</w:r>**
  <w:r w:rsidRPr="001C1D1B">
    <w:rPr>
      <w:shd w:val="clear" w:color="auto" w:fill="FABF8F"/>
    </w:rPr>
    <w:t>Member means any person who is enrolled in</w:t>
  </w:r>
  ....

Any help to convert the <w:t> </w:t> into <w:t> </w:t> is appreciated.

score 0 · Answer 1 · answered Jan 13 '16 at 20:20

0

Try let $newn := <w:t> </w:t> instead of let $newn := ''<w:t> </w:t>''.

answered Jan 13 '16 at 20:20

Martin Honnen

160,499
6
90
110

If I remove the '' and change the $newn := the gets replaced with nothing. – tstorli Jan 13 '16 at 21:23

score 0 · Answer 2 · answered Jan 14 '16 at 09:14

0

You have to add magic word (#ora:invalid_path empty #).More info here paragraph "Oracle XQuery Extension-Expression Pragmas"

UPDATE XML_TORTEST po
  SET po.XMLDOC =
    XMLQuery('
            declare namespace  w="http://schemas.openxmlformats.org/wordprocessingml/2006/main";   
              (#ora:invalid_path empty #){
              copy $i := $p1
              modify
              (
             for $j in $i/w:document/w:body/w:p/w:r/w:tab
             let $newn := <w:t></w:t>
             return replace node $j with $newn)
             return $i
             }
             ' PASSING XMLDOC AS "p1" 
          RETURNING CONTENT);

answered Jan 14 '16 at 09:14

Arkadiusz Łukasiewicz

6,241
1
11
17

I tried the update statement above, but no luck. It creates an empty text node. Like this ` 1.7 ` I need one that has a space character in it. – tstorli Jan 15 '16 at 15:33
I am able to create a node, but only if I put a value in it. Like this: ` UPDATE XML_TORTEST po ... ... let $newn := ### return replace node $j with $newn) return $i}' PASSING po.XMLDOC AS "p1" RETURNING CONTENT) WHERE XML_TORTEST_ID = 1 ...` But now I have to find a way to remove the ### characters so that only the space remains. – tstorli Jan 15 '16 at 15:33
`select xmlquery('let $spaceChar:= " " return {$spaceChar}' returning content) from dual;` Try this. And probably you can remove (#ora:invalid_path empty #) from code – Arkadiusz Łukasiewicz Jan 15 '16 at 16:20