Get Model meta data for a model site from Planning database

While most of Planning system’s meta data is stored in native relational tables in the Planning database, there are some meta data is stored in xml format in the database, for example, model meta data. You may need to get model meta data from the Planning databases. Here is a sample SQL query to get the model meta data for a specific model site from the Planning databases . The following example uses model site label of “Corporate”. Please note that this is only valid for the current released version of Planning system, the future release may change the way how these meta data is stored.

DECLARE @xmlblob xml

SELECT @xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX)) as xml)

FROM BizAppNodes ba

WHERE VersionEndDateTime = '9999-12-31 00:00:00.000'

AND BizAppNodeLabel = 'Corporate'

SELECT DISTINCT

tab.col.value('@Id', 'varchar(30)') as ModelId,

tab.col.value('@Label', 'varchar(40)') as ModelLabel

FROM @xmlblob.nodes ('/BizModelSite/Models/ArrayOfBizModel/BizModel') as tab(col)