How can I update a GLOBAL Shared Variable value directly with SQL ?

Follow

Kyle D Lemieux

• March 07, 2022 19:26

We are tasked with doing a mass update of various GLOBAL SHARED VARIABLE values.  We could do this via the UI, but that is tedious & time-consuming, and not really feasible given the data set size.  

I looked into the DB schema, and foudn the table WF_CUSTOM_ATTRS which I believe has the information regarding SV's ( app & global ).  In the attr_xml column, I see key/value pairs, which I assume has the values for the SV in it ? 

THE ASK:  How can I use SQL to select && update the value of a given GLOBAL Shared Var ?

0

• 

  Sanjay

  • March 07, 2022 21:56

  1. Select:

  SELECT ATTR_XML
    FROM [AP_Workflow_DB].[dbo].[WF_CUSTOM_ATTRS]
    where CUSTOM_ID = 'GLOBAL:{F09E4B16-466D-41a7-8EC2-0936D9D5DDE1}'

  Sample:

  <?xml version="1.0" encoding="utf-8"?><ArrayOfNameValue xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <NameValue><Name>test1</Name><Value xsi:type="xsd:string">abc</Value></NameValue>
    <NameValue><Name>test2</Name><Value xsi:type="xsd:string">xyz</Value></NameValue>
    </ArrayOfNameValue>

  2. Update:

  You may follow these articles on how you can update XML in SQL

  https://docs.microsoft.com/en-us/sql/t-sql/xml/replace-value-of-xml-dml?view=sql-server-ver15

  https://www.sqlshack.com/different-ways-to-update-xml-using-xquery-in-sql-server/

   

  0

  Comment actions Permalink

Please sign in to leave a comment.