Support SQL Server XML modify()

[tvedtorama]

I'm trying to do a:

UPDATE ball SET [cmdList].modify('insert <val>1030</val> into (/cmdList)[1]'), ogga.modify('insert <val>30</val> into (/ogga)[1]') WHERE [ballId] = @param3"

Or actually, I wanted the inserts to be sql arguments as well, but that does not seem to fit very well with SQL server rigid regime.

I ended up assigning the modify string to the json key in the $set, and setting the value (sql argument) to null. Then I had to do a nasty regex to ret rid of the argument part and some misplaced brackets:

const nastyModifyRegex = /\[modify\((.+?)\[1\]\]?\)\]\s?=\s?@param\d+/g

const correctNastyModifyFunction = (output: ISQLlyThings) => ({...output, sql: output.sql.replace(nastyModifyRegex, "modify($1[1])")})

It would be nice to have support for modify in the library. I tried to read up on operators, but it seems this needs to be built into the library, no plugin-system available.

[planetarydev]

You are right. There is no plugin-system, because I would have all changes done by the community should end up in a pull request. So feel free to fork this repo and add a new helper "modify" for SQL-Server and make a pull request.

If you need some help creating new operators and helper pleas let me know.

[tvedtorama]

I would love to do so if I find the time. I believe this library serves an important purpose, especially if it has wide support for the various SQL-dialects.

Do you see any fundamental problems building support for the .function('inline text') style of constructs? Where there are no SQL parameter involved?

[planetarydev]

There should be no problem writing the new modify-helper class. For Inline-SQL use the Helper "__" https://github.com/planetarydev/json-sql-builder2/tree/master/sql/helpers/misc/__.

[planetarydev]

I would suggest some usage like this:

sql.$update({
    $table: "ball",
    $setXML: {
        cmdList: { $modifyXML: { __: "insert <val>1030</val> into (/cmdList)[1]" } }
    },
    $where: {
        ballId: 1234
    }
}

In this case you need to write the $setXML helper and $modifyXML helper. You can't use the "normal" $set helper because this will assign a new value to a identifier. But you need a method-call like ".modify(...)"

[tvedtorama]

Thanks,

this makes sense. I look into it when if I get a chance.