Can't set SQL XML node level with in path

Question

I am attempting to modify a XML data type field with the SQL update statement below. I am having trouble setting the correct path past the level /Operand xsi:type="QueryObjectKey"/ to reach the /Name/ field. I can set the path to modify parent fields/values like /GroupBegin/false/GroupBegin/ with no problem, so I know the SQL statement is correct, just the path syntax to reach /Name/ is not right.

I get this ambiguous error message

Msg 2205, Level 16, State 1, Line 6
XQuery [ACVSCore.Access.Query.XMLEncodedCriteria.modify()]: ")" was expected.

How do I set that path to include the /Operand xsi:type="QueryObjectKey"/

DECLARE @NewValue Varchar(255) =  'None'

update
[ACVSCore].[Access].[Query]
SET
    XMLEncodedCriteria.modify('replace value of 
    (/QueryExpression/Criteria/CriteriaExpression/Operand xsi:type="QueryObjectKey"/Index/text())[1] with sql:variable("@NewValue")')

<QueryExpression xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" PrimaryObjectType="SoftwareHouse.NextGen.Common.SecurityObjects.Personnel" DataFetcherType="SoftwareHouse.CrossFire.Common.Objects.DataQuery">
  <DataFetcherTag xsi:type="xsd:string" />
  <Criteria>
    <CriteriaExpression>
      <GroupBegin>false</GroupBegin>
      <Operand xsi:type="QueryObjectKey">
        <Name>ORIGINAL</Name>
      </Operand>
    </CriteriaExpression>
  </Criteria>
</QueryExpression>

`/QueryExpression/Criteria/CriteriaExpression/Operand xsi:type="QueryObjectKey"/Index/text()` is not a valid XPath. If you meant so say "`Operand` node that has attribute `xsi:type` equal to `QueryObjectKey`", that would be `/QueryExpression/Criteria/CriteriaExpression/Operand[@xsi:type="QueryObjectKey"]/Index/text()`. — GSerg, Dec 20 '19 at 14:09

Yitzhak Khabinsky · Answer 1 · 2019-12-20T14:36:11.383

0

Your XML is using a namespace without its declaration. First, you need to fix the XML, and after that modify the desired XML element value. For the reference: What is the difference between xsd and xsi?

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT INTO @tbl (xml_data) 
VALUES ('<QueryExpression xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                 PrimaryObjectType="SoftwareHouse.NextGen.Common.SecurityObjects.Personnel"
                 DataFetcherType="SoftwareHouse.CrossFire.Common.Objects.DataQuery">
    <DataFetcherTag xsi:type="xsd:string"/>
    <Criteria>
        <CriteriaExpression>
            <GroupBegin>false</GroupBegin>
            <Operand xsi:type="QueryObjectKey">
                <Name>ORIGINAL</Name>
            </Operand>
        </CriteriaExpression>
    </Criteria>
</QueryExpression>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @NewValue VARCHAR(10) = 'None';
UPDATE @tbl
SET xml_data.modify('replace value of 
(/QueryExpression/Criteria/CriteriaExpression/Operand/Name/text())[1] with (sql:variable("@NewValue"))');

-- after
SELECT * FROM @tbl;

edited Dec 20 '19 at 14:36

answered Dec 19 '19 at 17:10

Yitzhak Khabinsky

18,471
2
15
21

Thanks Yitzhak, I appriciate your efforts. I did as you structed, however, the ORGINAL value of /Name/ in @tbl still remains as its first value, I need it to be the new value of CHANGED. What am I missing? – GForce Dec 20 '19 at 13:43
``` DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML); INSERT INTO @tbl (xml_data) VALUES (' ORIGINAL '); ``` – GForce Dec 20 '19 at 13:44
``` SELECT * FROM @tbl; DECLARE @NewValue VARCHAR(10) = 'CHANGED'; UPDATE @tbl SET xml_data.modify('replace value of (/QueryExpression/Criteria/CriteriaExpression/Operand/Index/text())[1] with (sql:variable("@NewValue"))') SELECT * FROM @tbl ``` – GForce Dec 20 '19 at 13:51
I adjusted the answer based on the changed requirements. Check it out. – Yitzhak Khabinsky Dec 20 '19 at 14:39

Can't set SQL XML node level with in path

1 Answers1