How to Edit an XML Column on the fly from Table in SQLServer

I work with an OLAP Engine called Mondrian from Pentaho – which has a Schema which is defined in XML.

For our implementation this is stored in a Table in a SQLServer DB and in production I cannot stop the Application server which is JBOSS and which connects to SQLServer to read the XML from the Table to set up initial Mondrian Connection and modify the XML by hand and then restart it. This is deployed for multiple customers in a multi-tenant mode.

So I had to devise a way to change the Mondrian Schema on the fly for a very serious customer issue.

I describe below a T-SQL script to do it.

Look at the XML below. I have made is short for clarity purpose.

Our XML  Schema is about 500MB long for the MONDRIAN Engine for our Database.

<?xml version=”1.0″ encoding=”UTF-8″?>

<Schema name=”OMJ_APS_200812_3″>

.

.

<Cube name=”Prescriber Product_Package Period”>

<Table name=”RX_PRESC_PKG”/>

<DimensionUsage name=”ST_H” source=”ST_H” foreignKey=”AlignmentId”/>

.

<Measure name=”Dollars” caption=”$” column=”Dollars” aggregator=”sum” formatString=”#,###”/>

<Measure name=”SMP” caption=”SMP” column=”SMP” aggregator=”sum” formatString=”#,###”/>

<Measure name=”Dollars NUMBER_PRESCRIBER” caption=”$ NUMBER_PRESCRIBER” column=”PrescriberID” aggregator=”Count” formatString=”#,###”/>

</Cube>

</Schema>

Look at the last Measure

<Measure name=”Dollars NUMBER_PRESCRIBER” caption=”$ NUMBER_PRESCRIBER” column=”DOL_NUM_PRESCRIBER” aggregator=”Count” formatString=”#,###”/>

Look at the attribute in it

column=”PrescriberID”

Suppose I want to change it to

column=”DOL_NUM_PRESCRIBER”

The Script which does the above is given below – Part 1

Also assume I need to change the

aggregator=”Count”

to

aggregator=”distinct count”

The script which does the above is – Part 2

==============================================================================================================

DECLARE @strCommand varchar(max)

DECLARE @measurePersistentName varchar(1024)

DECLARE @measureGroupName varchar(1024)

set @measurePersistentName = ‘PrescriberID’

— backup the table before modifying the XML contents

select * into MONDRIAN_OLAP_SCHEMA_BACKUP from MONDRIAN_OLAP_SCHEMA

BEGIN

— Part 1   Modify the Column Name

set @strCommand = ‘

DECLARE @myDoc xml

DECLARE @finalStr varchar(max)

select @myDoc=SchemaXml from MONDRIAN_OLAP_SCHEMA

set @finalStr = ”<?xml version=”1.0″ encoding=”UTF-8″?>”

SET @myDoc.modify(”’ +

‘replace value of (/Schema/Cube[@name=”Prescriber Product_Package Period”]/Measure[@name=”‘ + @measurePersistentName + ‘ NUMBER_PRESCRIBER”]/@column)[1]

with (“DOL_NUM_PRESCRIBER”) ”’ + ‘)

set @finalStr = @finalStr + convert(varchar(max), @myDoc)

update MONDRIAN_OLAP_SCHEMA set SchemaXml = @finalStr

exec (@strCommand)

— Part 2 — Modify the Aggregator Function Name

set @strCommand = ‘

DECLARE @myDoc xml

DECLARE @finalStr varchar(max)

select @myDoc=SchemaXml from MONDRIAN_OLAP_SCHEMA

set @finalStr = ”<?xml version=”1.0″ encoding=”UTF-8″?>”

SET @myDoc.modify(”’ +

‘replace value of (/Schema/Cube[@name=”Prescriber Product_Package Period”]/Measure[@name=”‘ +  @measurePersistentName +  ‘ NUMBER_PRESCRIBER”]/@aggregator)[1]

with (“distinct count”) ”’ + ‘)

set @finalStr = @finalStr + convert(varchar(max), @myDoc)

update MONDRIAN_OLAP_SCHEMA set SchemaXml = @finalStr

exec (@strCommand)

END

==============================================================================================================

Advertisements

One response to “How to Edit an XML Column on the fly from Table in SQLServer

  1. Pingback: Angelina

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s