I have a table called Rules that has a numeric column RuleID and an XML column XMLData.

Here is the contents of XMLData for RuleID = 1:

<EP Name="C3EPIC" ReturnVar="AUTOMDWG">
    <Parameters>
        <Parameter Key="Server" Value='="Rules.MyRules"'
                   Description="Server"/>
        <Parameter Key="EPICLIBRARY" Value='="DEVPGM10"' Description="Library"/>
        <Parameter Key="Library" Value='="DEVPGM10"' Description=""/>
        <Parameter Key="ProgramName" Value='="MyProgram"' Description=""/>
        <Parameter Key="Function" Value="=HDWEFUNCA"
                   Description="Hardware MFG MODEL #"/>
        <Parameter Key="Trim" Value="=HDWETRIMA" Description="Hardware Trim"/>
    </Parameters>
    <OutputVariables/>
</EP>

I have 2 tasks:

1. Find every ruleID where the parameter key EPICLIBRARY is ="DEVPGM10"
2. Change DEVPGM10 to QAPGM10 in those rules

What I have started with so far, just to see if I could query into the XML, is this:

DECLARE @xml XML = (SELECT XMLData
                    FROM Rules 
                    WHERE ruleid = 1   
                    FOR XML AUTO, ELEMENTS, ROOT('TopLevel'))

SELECT
    epic.value('@key', 'nvarchar(255)') as AttrKey,    
    epic.value('@value', 'nvarchar(255)') as AttrVal  
FROM
    @xml.nodes('TopLevel/Rules/XMLData/EP/Parameters/Parameter') A(epic)

The good news is that it returns exactly 6 rows, however, for all 6 records, AttrKey and AttrVal are NULL.

I was expecting/hoping for AttrKey values of

Server
EPICLIBRARY
Library
ProgramName
Function
Trim

Thanks in advance for any guidance

I have a table called Rules that has a numeric column RuleID and an XML column XMLData.

Here is the contents of XMLData for RuleID = 1:

<EP Name="C3EPIC" ReturnVar="AUTOMDWG">
    <Parameters>
        <Parameter Key="Server" Value='="Rules.MyRules.com"'
                   Description="Server"/>
        <Parameter Key="EPICLIBRARY" Value='="DEVPGM10"' Description="Library"/>
        <Parameter Key="Library" Value='="DEVPGM10"' Description=""/>
        <Parameter Key="ProgramName" Value='="MyProgram"' Description=""/>
        <Parameter Key="Function" Value="=HDWEFUNCA"
                   Description="Hardware MFG MODEL #"/>
        <Parameter Key="Trim" Value="=HDWETRIMA" Description="Hardware Trim"/>
    </Parameters>
    <OutputVariables/>
</EP>

I have 2 tasks:

1. Find every ruleID where the parameter key EPICLIBRARY is ="DEVPGM10"
2. Change DEVPGM10 to QAPGM10 in those rules

What I have started with so far, just to see if I could query into the XML, is this:

DECLARE @xml XML = (SELECT XMLData
                    FROM Rules 
                    WHERE ruleid = 1   
                    FOR XML AUTO, ELEMENTS, ROOT('TopLevel'))

SELECT
    epic.value('@key', 'nvarchar(255)') as AttrKey,    
    epic.value('@value', 'nvarchar(255)') as AttrVal  
FROM
    @xml.nodes('TopLevel/Rules/XMLData/EP/Parameters/Parameter') A(epic)

The good news is that it returns exactly 6 rows, however, for all 6 records, AttrKey and AttrVal are NULL.

I was expecting/hoping for AttrKey values of

Server
EPICLIBRARY
Library
ProgramName
Function
Trim

Thanks in advance for any guidance

• sql-server
• xml
• t-sql
• xquery

• While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Commented 2 days ago

1 Answer 1

Please try the following solution.

The SQL Server XQuery's .modify() method has limitation that it is capable to update one single value only!

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (RuleID INT IDENTITY PRIMARY KEY, XMLData XML);
INSERT INTO @tbl (XMLData) VALUES
(N'<EP Name="C3EPIC" ReturnVar="AUTOMDWG">
    <Parameters>
        <Parameter Key="Server" Value=''="Rules.MyRules.com"''
                   Description="Server"/>
        <Parameter Key="EPICLIBRARY" Value=''="DEVPGM10"'' Description="Library"/>
        <Parameter Key="Library" Value=''="DEVPGM10"'' Description=""/>
        <Parameter Key="ProgramName" Value=''="MyProgram"'' Description=""/>
        <Parameter Key="Function" Value="=HDWEFUNCA"
                   Description="Hardware MFG MODEL #"/>
        <Parameter Key="Trim" Value="=HDWETRIMA" Description="Hardware Trim"/>
    </Parameters>
    <OutputVariables/>
</EP>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @replaceWith VARCHAR(30) = '="QAPGM10"';

UPDATE @tbl 
SET XMLData.modify('replace value of (/EP/Parameters/Parameter[@Key="EPICLIBRARY" and @Value=''="DEVPGM10"'']/@Value)[1] 
    with sql:variable("@replaceWith")');

-- after
SELECT * FROM @tbl;

SELECT RuleID
    , c.value('@Key','nvarchar(255)') AS AttrKey
    , c.value('@Value', 'nvarchar(255)') AS AttrVal 
FROM @tbl
CROSS APPLY XMLData.nodes('/EP/Parameters/Parameter') AS t(c);

Output