Sql-server – How to Return XML Node Ordinal, or delete node based on element value

sql-server-2008-r2xmlxquery

I have an XML document similar to this:

<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>

I am replacing an entire <Record> node based on some criteria. The <Record> nodes contain a <Guid> that I can use to identify them (pretend those are valid GUIDs please!).

Knowing the GUID, I am returning that node's XML into a variable for further processing. Downstream, I need to be able to delete that particular node so I that I can insert the modified version of the node back into the original document.

Is there a way to determine the ordinal, or another way to use a delete/replace method to remove a node based on an element's value?

Best Answer

If you have the value in a variable you can use sql:variable() in the predicate for the delete.

declare @XML xml = '
<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'

set @XML.modify('delete /Root/Sub/Record[Guid = sql:variable("@Guid")]')

Replacing a record with a new record would look something like this.

declare @XML xml = '
<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
      <Value>some value</Value>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '
<Record>
  <Guid>aslkjflaksjflkasjfkljsd</Guid>
  <Value>some new value</Value>
</Record>'

set @XML.modify('insert sql:variable("@NewRecord") after (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')
set @XML.modify('delete (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')

First add the new record after the existing one and the delete the first occurrence where Guid is a match. Without [1] in the delete you will delete all occurrences, not only the first one.

Related Solutions

XMLDB – Nodes to Rows

I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts.

First, my original question can be solved by using XMLTable:

SELECT 
  x.ID            AS "MEASUREMENT_ID", 
  x.GUID          AS "MEASUREMENT_GUID", 
  a.COLUMN_VALUE  AS "MESSAGE"
FROM SPERF_XML_RAW x,
  XMLTable('/PerfInfo/EventLog/EventRecord/@Provider'
            PASSING x.DATA_XML) a;

Note that COLUMN_VALUE is a pseudocolumn that can be used in conjunction with XMLTable

As additional information I would like to add the following. You can create multiple columns with the following syntax:

SELECT 
    x.ID,
    x.GUID,
    res."Provider", 
    res."TimeCreated"
FROM SPERF_XML_RAW x,
    XMLTABLE('/PerfInfo'
      PASSING x.DATA_XML
      COLUMNS 
         "Provider"     VARCHAR2(32 CHAR)   PATH '/PerfInfo/EventLog/EventRecord',
         "TimeCreated"  TIMESTAMP           PATH '/PerfInfo/EventLog/EventRecord') res;

But Oracle will throw ORA-19279: XPTY0004 when one or more columns return multiple nodes.

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

The solution to THAT problem can be found below:

SELECT
  x.ID              AS "MEASUREMENT_ID",
  x.GUID            AS "MEASUREMENT_GUID",
  res."TIMECREATED", 
  res."LOGNAME",
  res."LEVEL",
  res."ID",
  res."PROVIDER",
  res."MESSAGE"
FROM SPERF_XML_RAW x,
   XMLTable('/PerfInfo' 
      PASSING x.DATA_XML
      COLUMNS
        "EventRecords" XMLTYPE PATH 'EventLog') rec,
   XMLTABLE ('EventLog/EventRecord'
       PASSING rec."EventRecords"
       COLUMNS
        "LOGNAME"         VARCHAR(32 CHAR)      PATH '@LogName',
        "ID"              NUMBER                PATH '@ID',
        "LEVEL"           CHAR(1 CHAR)          PATH '@Level',
        "PROVIDER"        VARCHAR2(128 CHAR)    PATH '@Provider',
        "TIMECREATED"     VARCHAR2(32 CHAR)     PATH '@TimeCreated',
        "MESSAGE"         CLOB                  PATH 'text()') res;

This will probably fail once more as soon as your document has too many levels, and I have not included a document with namespaces, which will most likely cause this to explode as well.

All information was found in the XML DB Developers guide: https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm#g1055369

Sql-server – How to call modify() on a nodelist in T-SQL

Three options come to mind.

Use a while loop and modify one node at a time

Shred the XML using nodes(), modify the values and then rebuild using for xml.

Cast the XML to a string and use replace().

Whatever works best in your case depends on the shape of the XML and what kind of modification you need to do. If you add the table structures with sample data and expected output to the question you might get a better answer.

Best Answer

Related Solutions

XMLDB – Nodes to Rows

Sql-server – How to call modify() on a nodelist in T-SQL

Related Question