Problem
Many organizations still rely on unstructured document formats for strategic plans and performance reports. I have heard that StratML might help provide a structured, machine-readable format, among other things, needed in modern governance and management. What is StratML and how can we use it in SQL Server?
Solution
Strategy Markup Language (StratML) is an XML-based ANSI/ISO standard vocabulary and schema for information commonly contained in strategic performance plans and reports. It is divided in three parts:
- Part 1: Covers strategic plans (mission, vision, values, goals, and objectives); it defines why and what.
- Part 2: Extend to performance plans and reports (indicators, results, stakeholders, etc.); it defines how and who.
- Part 3: Focus on linking performance plans to actionable data and real-world execution, making the intentions truly trackable and interactive across systems, gaining interoperability, support for semantic web integration, performance reporting in real time, and traceability. This part connects to real-time data and tools.
There are several core elements of StratML:
- Organization: The legal or logical entity to which the plan or report applies.
- Mission: A brief description of the basic purpose of the organization
- Vision: A concise and inspirational statement implicitly relating an organization’s purpose to its values, thus motivating its actions toward a future state it strives to achieve
- Value: A principle that is important and helps to define the essential character of the organization
- Goal: A relatively broad statement of intended results to be achieved over more than one resource allocation and performance measurement cycle
- Objective: A target level of results against which achievement is to be measured within a single resource allocation and performance execution cycle
- Stakeholder: An individual whose interests can be affected by or whose action can be required in order to achieve a goal or objective
In summary: Vision inspires, Goals give shape, Objectives create action, Metrics track progress, all rooted in Mission.
The XML Namespace is the vocabulary of the elements inside the XML structure to avoid element name conflicts, same names but with different meanings. When a namespace is defined for an element, all child elements with the same prefix are associated with the same namespace.
Using StratML in SQL Server
Below is a StratML database diagram.
Table Strategic Plan
A document identifying inputs and processes required to accomplish objectives and establishing metrics by which progress will be assessed within a single budgetary planning and resource allocation cycle.
CREATE TABLE [dbo].[SmlStrategicPlan](
[StratPlanName] [nvarchar](250) NOT NULL,
[StratPlanDocumentType] [nvarchar](50) NOT NULL,
[StratPlanLanguage] [nvarchar](10) DEFAULT (N'en'),
[StratPlanDescription] [nvarchar](max) NULL,
[StratPlanOtherInfo] [nvarchar](max) NULL,
[AdmInfoIdentifier] [nvarchar](50) NULL,
[AdmInfoStartDate] [date] NULL,
[AdmInfoEndDate] [date] NULL,
[AdmInfoPublicationDate] [date] NULL,
[AdmInfoSource] [nvarchar](max) NULL,
[SubmitterIdentifier] [nvarchar](50) NULL,
[SubmitterGivenName] [nvarchar](80) NULL,
[SubmitterSurname] [nvarchar](70) NULL,
[SubmitterPhoneNumber] [nvarchar](50) NULL,
[SubmitterEmailAddress] [nvarchar](150) NULL,
CONSTRAINT [PK_SmlStrategicPlan] PRIMARY KEY CLUSTERED
(
[StratPlanName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOTable Strategic Plan Core
A complex element comprised of Mission, Vision, Values, Goals, Objectives and Stakeholders, together with the children of those core elements.
CREATE TABLE [dbo].[SmlStrategicPlanCore](
[StratPlanCoreId] [int] IDENTITY(1,1) NOT NULL,
[StratPlanName] [nvarchar](250) NULL,
[Vision_Description] [nvarchar](max) NULL,
[Vision_Identifier] [nvarchar](50) NULL,
[Mission_Description] [nvarchar](max) NULL,
[Mission_Identifier] [nvarchar](50) NULL,
CONSTRAINT [PK_SmlStrategicPlanCore] PRIMARY KEY CLUSTERED
(
[StratPlanCoreId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmlStrategicPlanCore] WITH CHECK ADD CONSTRAINT [FK_SmlStrategicPlanCore_SmlStrategicPlan] FOREIGN KEY([StratPlanName])
REFERENCES [dbo].[SmlStrategicPlan] ([StratPlanName])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SmlStrategicPlanCore] CHECK CONSTRAINT [FK_SmlStrategicPlanCore_SmlStrategicPlan]
GOTable Organizations
The legal or logical entity to which the plan or report applies.
CREATE TABLE [dbo].[SmlOrganizations](
[OrgIdentifier] [nvarchar](50) NOT NULL,
[StratPlanCoreId] [int] NULL,
[OrgType] [nvarchar](50) NULL,
[OrgAcronym] [nvarchar](50) NULL,
[OrgName] [nvarchar](250) NULL,
[OrgDescription] [nvarchar](max) NULL,
[OrgWebsite] [nvarchar](50) NULL,
CONSTRAINT [PK_SmlOrganizations] PRIMARY KEY CLUSTERED
(
[OrgIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmlOrganizations] WITH CHECK ADD CONSTRAINT [FK_SmlOrganizations_SmlStrategicPlanCore] FOREIGN KEY([StratPlanCoreId])
REFERENCES [dbo].[SmlStrategicPlanCore] ([StratPlanCoreId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SmlOrganizations] CHECK CONSTRAINT [FK_SmlOrganizations_SmlStrategicPlanCore]
GOTable Stakeholders
An individual whose interests may be affected by or whose action may be required to achieve a goal or objective.
CREATE TABLE [dbo].[SmlStakeholders](
[Identifier] [nvarchar](50) NOT NULL,
[StakeholderName] [nvarchar](250) NOT NULL,
[StakeholderType] [nvarchar](50) NULL,
[StakeholderDescription] [nvarchar](max) NULL,
[StakeholderPointOfContact] [nvarchar](500) NULL,
CONSTRAINT [PK_SmlStakeholders_1] PRIMARY KEY CLUSTERED
(
[Identifier] ASC,
[StakeholderName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOTable Roles
A complex element containing other elements enabling the naming, description, and categorization of different kinds of interest’s individuals and groups may have in the achievement of goals and objectives.
CREATE TABLE [dbo].[SmlRoles](
[Identifier] [nvarchar](50) NOT NULL,
[StakeholderName] [nvarchar](250) NOT NULL,
[RoleName] [nvarchar](50) NOT NULL,
[RoleDescription] [nvarchar](max) NULL,
[RoleType] [nvarchar](50) NULL,
CONSTRAINT [PK_SmlRoles] PRIMARY KEY CLUSTERED
(
[Identifier] ASC,
[StakeholderName] ASC,
[RoleName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOTable Values
A principle that is important and helps to define the essential character of the organization.
CREATE TABLE [dbo].[SmlValues](
[StratPlanCoreId] [int] NOT NULL,
[ValueName] [nvarchar](250) NOT NULL,
[ValueDesc] [nvarchar](max) NULL,
CONSTRAINT [PK_SmlValues_1] PRIMARY KEY CLUSTERED
(
[StratPlanCoreId] ASC,
[ValueName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmlValues] WITH CHECK ADD CONSTRAINT [FK_SmlValues_SmlStrategicPlanCore] FOREIGN KEY([StratPlanCoreId])
REFERENCES [dbo].[SmlStrategicPlanCore] ([StratPlanCoreId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SmlValues] CHECK CONSTRAINT [FK_SmlValues_SmlStrategicPlanCore]
GOTable Goals
A relatively broad statement of intended results to be achieved over more than one resource allocation and performance measurement cycle. Supports Mission.
CREATE TABLE [dbo].[SmlGoals](
[GoalIdentifier] [nvarchar](50) NOT NULL,
[StratPlanCoreId] [int] NOT NULL,
[GoalName] [nvarchar](250) NULL,
[GoalDescription] [nvarchar](max) NULL,
[GoalSequenceIndicator] [nvarchar](50) NULL,
[GoalOtherInformation] [nvarchar](max) NULL,
CONSTRAINT [PK_SmlGoals] PRIMARY KEY CLUSTERED
(
[GoalIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmlGoals] WITH CHECK ADD CONSTRAINT [FK_SmlGoals_SmlStrategicPlanCore] FOREIGN KEY([StratPlanCoreId])
REFERENCES [dbo].[SmlStrategicPlanCore] ([StratPlanCoreId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SmlGoals] CHECK CONSTRAINT [FK_SmlGoals_SmlStrategicPlanCore]
GOTable Objectives
A target level of results against which achievement is to be measured within a single resource allocation and performance execution cycle.
CREATE TABLE [dbo].[SmlObjectives](
[ObjectiveIdentifier] [nvarchar](50) NOT NULL,
[GoalIdentifier] [nvarchar](50) NOT NULL,
[ObjectiveName] [nvarchar](250) NULL,
[ObjectiveDescription] [nvarchar](max) NULL,
[ObjectiveSequenceIndicator] [nvarchar](50) NULL,
[ObjectiveOtherInformation] [nvarchar](max) NULL,
CONSTRAINT [PK_SmlObjectives] PRIMARY KEY CLUSTERED
(
[ObjectiveIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmlObjectives] WITH CHECK ADD CONSTRAINT [FK_SmlObjectives_SmlGoals] FOREIGN KEY([GoalIdentifier])
REFERENCES [dbo].[SmlGoals] ([GoalIdentifier])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SmlObjectives] CHECK CONSTRAINT [FK_SmlObjectives_SmlGoals]
GOTable Performance Indicator
A measurement dimension, unit of measurement, and measurement instance specifying target and actual results, e.g., of inputs applied, outputs produced, processes conducted, and outcomes realized.
CREATE TABLE [dbo].[SmlPerformanceIndicator](
[ObjectiveIdentifier] [nvarchar](50) NOT NULL,
[ObjectiveSequenceIndicator] [nvarchar](50) NOT NULL,
[PerfIndIdentifier] [nvarchar](50) NOT NULL,
[PerfIndSequenceIndicator] [nvarchar](50) NULL,
[PerfIndValueChainStage] [nvarchar](50) NULL,
[PerfIndType] [nvarchar](50) NULL,
[PerfIndKeyness] [nvarchar](50) NULL,
[PerfIndMeasurementDimension] [nvarchar](50) NULL,
[PerfIndUnitOfMeasurement] [nvarchar](50) NULL,
[PerfIndOtherInformation] [nvarchar](max) NULL,
CONSTRAINT [PK_SmlPerformanceIndicator] PRIMARY KEY CLUSTERED
(
[ObjectiveIdentifier] ASC,
[ObjectiveSequenceIndicator] ASC,
[PerfIndIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmlPerformanceIndicator] WITH CHECK ADD CONSTRAINT [FK_SmlPerformanceIndicator_SmlObjectives] FOREIGN KEY([ObjectiveIdentifier])
REFERENCES [dbo].[SmlObjectives] ([ObjectiveIdentifier])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SmlPerformanceIndicator] CHECK CONSTRAINT [FK_SmlPerformanceIndicator_SmlObjectives]
GOTable Relationship
A conceptual association between two elements.
CREATE TABLE [dbo].[SmlRelationship](
[PerfIndIdentifier] [nvarchar](50) NOT NULL,
[PerfIndSequenceIndicator] [nvarchar](50) NOT NULL,
[RelIdentifier] [nvarchar](50) NOT NULL,
[RelType] [nvarchar](50) NULL,
[RelName] [nvarchar](250) NULL,
[RelDescription] [nvarchar](max) NULL,
CONSTRAINT [PK_SmlRelationship] PRIMARY KEY CLUSTERED
(
[PerfIndIdentifier] ASC,
[PerfIndSequenceIndicator] ASC,
[RelIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOTable Relationship Referent Identifier
The Identifier of the target (object) of a relationship.
CREATE TABLE [dbo].[SmlRelationshipRefId](
[RelIdentifier] [nvarchar](50) NOT NULL,
[RelReferentIdentifier] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_SmlRelationshipRefIdentifier] PRIMARY KEY CLUSTERED
(
[RelIdentifier] ASC,
[RelReferentIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOTable Measurement Instance
A measurement at a point in time.
CREATE TABLE [dbo].[SmlMeasurementInstance](
[PerfIndIdentifier] [nvarchar](50) NOT NULL,
[PerfIndSequenceIndicator] [nvarchar](50) NOT NULL,
[ResultType] [nvarchar](10) NOT NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL,
[NumberOfUnits] [decimal](18, 2) NULL,
[DescriptorName] [nvarchar](250) NULL,
[DescriptorValue] [nvarchar](250) NULL,
[Description] [nvarchar](250) NULL,
CONSTRAINT [PK_MeasurementInstance] PRIMARY KEY CLUSTERED
(
[PerfIndIdentifier] ASC,
[PerfIndSequenceIndicator] ASC,
[ResultType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOStored Procedure StratML File Import
The queries inside this store procedure will be created using a specific namespace PerformancePlanOrReport’. In case your sample has a different one, make the necessary changes in all queries before running.
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20250428
-- Description: StratML import
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspStratMLImport]
(@XmlFilePathAndName nvarchar(500))
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@XmlFilePathAndName) = 0 OR @XmlFilePathAndName IS NULL
RETURN;
BEGIN TRY
BEGIN TRANSACTION
-- VARIABLES =====================================================================================
DECLARE @StratML XML
,@StratMLStr nvarchar(MAX)
,@sql NVARCHAR(MAX)
,@StrategicPlanName nvarchar(250);
SET @sql = N'SELECT @xmlOut = BULKDATA FROM OPENROWSET
(BULK ''' + @XmlFilePathAndName +
''',SINGLE_CLOB) AS BulkData(BulkData)';
EXEC sp_executesql @sql,N'@xmlOut NVARCHAR(MAX) OUTPUT',@xmlOut = @StratMLStr OUTPUT;
-- FIX RESERVED TERMS INSIDE XML =================================================================
SET @StratMLStr = REPLACE(@StratMLStr,'&','&');
SET @StratML = @StratMLStr;
-- RESETING ALL TABLES ===========================================================================
DELETE FROM [dbo].[SmlStrategicPlan];
DELETE FROM [dbo].[SmlStrategicPlanCore];
DBCC CHECKIDENT ('dbo.SmlStrategicPlanCore', RESEED, 0);
DELETE FROM [dbo].[SmlMeasurementInstance];
DELETE FROM [dbo].[SmlRelationshipRefId];
DELETE FROM [dbo].[SmlRelationship];
DELETE FROM [dbo].[SmlPerformanceIndicator];
DELETE FROM [dbo].[SmlObjectives];
DELETE FROM [dbo].[SmlGoals];
DELETE FROM [dbo].[SmlValues];
DELETE FROM [dbo].[SmlRoles];
DELETE FROM [dbo].[SmlStakeholders];
DELETE FROM [dbo].[SmlOrganizations];
-- STRATEGIC PLAN ================================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlStrategicPlan]
([StratPlanDocumentType]
,[StratPlanLanguage]
,[StratPlanName]
,[StratPlanDescription]
,[StratPlanOtherInfo]
,[AdmInfoIdentifier]
,[AdmInfoStartDate]
,[AdmInfoEndDate]
,[AdmInfoPublicationDate]
,[AdmInfoSource]
,[SubmitterIdentifier]
,[SubmitterGivenName]
,[SubmitterSurname]
,[SubmitterPhoneNumber]
,[SubmitterEmailAddress])
SELECT SP.value('(@Type)[1]','nvarchar(50)') AS spType
,NULLIF(SP.value('(@Language)[1]','nvarchar(50)'),'') AS spLanguage
,SP.value('(Name)[1]','nvarchar(250)') AS spName
,SP.value('(Description)[1]','nvarchar(250)') AS spDescription
,SP.value('(OtherInformation)[1]','nvarchar(MAX)') AS OtherInformation
,SP.value('(AdministrativeInformation/Identifier)[1]','nvarchar(50)') AS AdmInfoIdentifier
,NULLIF(SP.value('(AdministrativeInformation/StartDate)[1]','nvarchar(50)'),'') AS AdmInfoStartDate
,NULLIF(SP.value('(AdministrativeInformation/EndDate)[1]','nvarchar(50)'),'') AS AdmInfoEndDate
,NULLIF(SP.value('(AdministrativeInformation/PublicationDate)[1]','nvarchar(50)'),'') AS AdmInfoPublicationDate
,SP.value('(AdministrativeInformation/Source)[1]','nvarchar(MAX)') AS AdmInfoSource
,SP.value('(Submitter/Identifier)[1]','nvarchar(50)') AS SubmitterIdentifier
,SP.value('(Submitter/GivenName)[1]','nvarchar(80)') AS SubmitterGivenName
,SP.value('(Submitter/Surname)[1]','nvarchar(80)') AS SubmitterSurname
,NULLIF(SP.value('(Submitter/PhoneNumber)[1]','nvarchar(50)'),'') AS SubmitterPhoneNumber
,NULLIF(SP.value('(Submitter/EmailAddress)[1]','nvarchar(500)'),'') AS SubmitterEmailAddress
FROM @StratML.nodes('PerformancePlanOrReport') AS T2(SP);
SELECT @StrategicPlanName = [StratPlanName]
FROM [dbo].[SmlStrategicPlan];
-- STRATEGIC PLAN CORE ===========================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlStrategicPlanCore]
([StratPlanName]
,[Vision_Description]
,[Vision_Identifier]
,[Mission_Description]
,[Mission_Identifier])
SELECT @StrategicPlanName
,Det.value('(Vision/Description)[1]','nvarchar(MAX)') AS Vision
,Det.value('(Vision/Identifier)[1]','nvarchar(MAX)') AS VisionId
,Det.value('(Mission/Description)[1]','nvarchar(MAX)') AS Mission
,Det.value('(Mission/Identifier)[1]','nvarchar(MAX)') AS MissionId
FROM @StratML.nodes('PerformancePlanOrReport/StrategicPlanCore') AS T2(Det);
DECLARE @StrategicPlanCoreId int = SCOPE_IDENTITY();
-- ORGANIZATION ==================================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlOrganizations]
([OrgName]
,[OrgAcronym]
,[OrgIdentifier]
,[OrgDescription]
,[OrgType])
SELECT Org.value('(Name)[1]','nvarchar(250)') AS [Name]
,Org.value('(Acronym)[1]','nvarchar(50)') AS [Acronym]
,Org.value('(Identifier)[1]','nvarchar(50)') AS [Identifier]
,NULLIF(Org.value('(Description)[1]','nvarchar(MAX)'),'') AS [Description]
,NULLIF(Org.value('(@SpecialOrganizationTypeType)[1]','nvarchar(50)'),'') AS [Identifier]
FROM @StratML.nodes('PerformancePlanOrReport/StrategicPlanCore/Organization') AS T2(Org);
-- ORGANIZATION STAKEHOLDERS =====================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlStakeholders]
([Identifier]
,[StakeholderType]
,[StakeholderName]
,[StakeholderDescription]
,[StakeholderPointOfContact])
SELECT OShdr.value('(../Identifier)[1]','nvarchar(50)') AS OrgIdentifier
,NULLIF(OShdr.value('(@StakeholderTypeType)[1]','nvarchar(50)'),'') AS StakeholderType
,OShdr.value('(Name)[1]','nvarchar(250)') AS StakeholderName
,NULLIF(OShdr.value('(Description)[1]','nvarchar(MAX)'),'') AS StakeholderDescription
,NULLIF(OShdr.value('(PointOfContact)[1]','nvarchar(500)'),'') AS StakeholderPointOfContact
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Organization/Stakeholder') AS XTbl(OShdr);
-- ORGANIZATION STAKEHOLDER ROLES ================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlRoles]
([Identifier]
,[StakeholderName]
,[RoleName]
,[RoleDescription]
,[RoleType])
SELECT Roles.value('(../../Identifier)[1]','nvarchar(50)') AS Identifier
,Roles.value('(../Name)[1]','nvarchar(250)') AS StakeholderName
,Roles.value('(Name)[1]','nvarchar(50)') AS StakeholderRoleName
,NULLIF(Roles.value('(Description)[1]','nvarchar(MAX)'),'') AS StakeholderRoleDescription
,NULLIF(Roles.value('(RoleType)[1]','nvarchar(50)'),'') AS StakeholderRoleType
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Organization/Stakeholder/Role') AS XTbl(Roles);
-- VALUES ========================================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlValues]
([StratPlanCoreId]
,[ValueName]
,[ValueDesc])
SELECT @StrategicPlanCoreId
,sValue.value('(Name)[1]','nvarchar(250)') AS ValueName
,sValue.value('(Description)[1]','nvarchar(MAX)') AS ValueDescription
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Value') AS XTbl(sValue);
-- GOALS =========================================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlGoals]
([StratPlanCoreId]
,[GoalName]
,[GoalDescription]
,[GoalIdentifier]
,[GoalSequenceIndicator]
,[GoalOtherInformation])
SELECT @StrategicPlanCoreId
,Goal.value('(Name)[1]','nvarchar(250)') AS GoalName
,Goal.value('(Description)[1]','nvarchar(MAX)') AS GoalDescription
,Goal.value('(Identifier)[1]','nvarchar(50)') AS GoalIdentifier
,Goal.value('(SequenceIndicator)[1]','nvarchar(50)') AS GoalSequenceIndicator
,Goal.value('(OtherInformation)[1]','nvarchar(MAX)') AS GoalOtherInformation
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal') AS XTbl(Goal);
-- GOALS STAKEHOLDERS =====================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlStakeholders]
([Identifier]
,[StakeholderType]
,[StakeholderName]
,[StakeholderDescription]
,[StakeholderPointOfContact])
SELECT OShdr.value('(../Identifier)[1]','nvarchar(50)') AS OrgIdentifier
,NULLIF(OShdr.value('(@StakeholderTypeType)[1]','nvarchar(50)'),'') AS StakeholderType
,OShdr.value('(Name)[1]','nvarchar(250)') AS StakeholderName
,NULLIF(OShdr.value('(Description)[1]','nvarchar(MAX)'),'') AS StakeholderDescription
,NULLIF(OShdr.value('(PointOfContact)[1]','nvarchar(500)'),'') AS StakeholderPointOfContact
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Stakeholder') AS XTbl(OShdr);
-- GOALS STAKEHOLDER ROLES ================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlRoles]
([Identifier]
,[StakeholderName]
,[RoleName]
,[RoleDescription]
,[RoleType])
SELECT Roles.value('(../../Identifier)[1]','nvarchar(50)') AS Identifier
,Roles.value('(../Name)[1]','nvarchar(250)') AS StakeholderName
,Roles.value('(Name)[1]','nvarchar(50)') AS StakeholderRoleName
,NULLIF(Roles.value('(Description)[1]','nvarchar(MAX)'),'') AS StakeholderRoleDescription
,NULLIF(Roles.value('(RoleType)[1]','nvarchar(50)'),'') AS StakeholderRoleType
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Stakeholder/Role') AS XTbl(Roles);
-- GOAL OBJECTIVES ===============================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlObjectives]
([GoalIdentifier]
,[ObjectiveName]
,[ObjectiveDescription]
,[ObjectiveIdentifier]
,[ObjectiveSequenceIndicator]
,[ObjectiveOtherInformation])
SELECT Obj.value('(../Identifier)[1]','nvarchar(50)') AS GoalIdentifier
,Obj.value('(Name)[1]','nvarchar(250)') AS ObjectiveName
,Obj.value('(Description)[1]','nvarchar(MAX)') AS ObjectiveDescription
,Obj.value('(Identifier)[1]','nvarchar(50)') AS ObjectiveIdentifier
,Obj.value('(SequenceIndicator)[1]','nvarchar(50)') AS ObjectiveSequenceIndicator
,NULLIF(Obj.value('(OtherInformation)[1]','nvarchar(MAX)'),'') AS ObjectiveOtherInformation
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective') AS XTbl(Obj);
-- OBJECTIVE STAKEHOLDERS ========================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlStakeholders]
([Identifier]
,[StakeholderType]
,[StakeholderName]
,[StakeholderDescription]
,[StakeholderPointOfContact])
SELECT OShdr.value('(../Identifier)[1]','nvarchar(50)') AS OrgIdentifier
,NULLIF(OShdr.value('(@StakeholderTypeType)[1]','nvarchar(50)'),'') AS StakeholderType
,OShdr.value('(Name)[1]','nvarchar(250)') AS StakeholderName
,NULLIF(OShdr.value('(Description)[1]','nvarchar(MAX)'),'') AS StakeholderDescription
,NULLIF(OShdr.value('(PointOfContact)[1]','nvarchar(500)'),'') AS StakeholderPointOfContact
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/Stakeholder') AS XTbl(OShdr);
-- OBJECTIVE STAKEHOLDER ROLES ===================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlRoles]
([Identifier]
,[StakeholderName]
,[RoleName]
,[RoleDescription]
,[RoleType])
SELECT Roles.value('(../../Identifier)[1]','nvarchar(50)') AS Identifier
,Roles.value('(../Name)[1]','nvarchar(250)') AS StakeholderName
,Roles.value('(Name)[1]','nvarchar(50)') AS StakeholderRoleName
,NULLIF(Roles.value('(Description)[1]','nvarchar(MAX)'),'') AS StakeholderRoleDescription
,NULLIF(Roles.value('(RoleType)[1]','nvarchar(50)'),'') AS StakeholderRoleType
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/Stakeholder/Role') AS XTbl(Roles)
WHERE Roles.value('(Name)[1]','nvarchar(50)') <> '';
-- OBJECTIVE PERFORMANCE =========================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlPerformanceIndicator]
([ObjectiveIdentifier]
,[ObjectiveSequenceIndicator]
,[PerfIndIdentifier]
,[PerfIndSequenceIndicator]
,[PerfIndValueChainStage]
,[PerfIndType]
,[PerfIndKeyness]
,[PerfIndMeasurementDimension]
,[PerfIndUnitOfMeasurement]
,[PerfIndOtherInformation])
SELECT PerfInd.value('(../Identifier)[1]','nvarchar(50)') AS ObjectiveIdentifier
,PerfInd.value('(../SequenceIndicator)[1]','nvarchar(50)') AS ObjectiveSequenceIndicator
,PerfInd.value('(Identifier)[1]','nvarchar(50)') AS PerfIndIdentifier
,NULLIF(PerfInd.value('(SequenceIndicator)[1]','nvarchar(50)'),'') AS PerfIndSequenceIndicator
,NULLIF(PerfInd.value('(@ValueChainStage)[1]','nvarchar(50)'),'') AS PerfIndSequenceIndicator
,NULLIF(PerfInd.value('(@PerformanceIndicatorTypeType)[1]','nvarchar(50)'),'') AS PerfIndSequenceIndicator
,NULLIF(PerfInd.value('(@Keyness)[1]','nvarchar(50)'),'') AS PerfIndSequenceIndicator
,NULLIF(PerfInd.value('(MeasurementDimension)[1]','nvarchar(50)'),'') AS PerfIndMeasurementDimension
,NULLIF(PerfInd.value('(UnitOfMeasurement)[1]','nvarchar(50)'),'') AS PerfIndUnitOfMeasurement
,NULLIF(PerfInd.value('(OtherInformation)[1]','nvarchar(MAX)'),'') AS PerfIndOtherInformation
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/PerformanceIndicator') AS XTbl(PerfInd);
-- PERFORMANCE RELATIONSHIP ======================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlRelationship]
([PerfIndIdentifier]
,[PerfIndSequenceIndicator]
,[RelIdentifier]
,[RelType]
,[RelName]
,[RelDescription])
SELECT PerfInd.value('(Identifier)[1]','nvarchar(50)') AS PerfIndIdentifier
,PerfInd.value('(SequenceIndicator)[1]','nvarchar(50)') AS PerfIndSequenceIndicator
,Relationship.value('(Identifier)[1]','nvarchar(50)') AS RelIdentifier
,NULLIF(Relationship.value('(@RelationshipType)[1]','nvarchar(50)'),'') AS RelType
,NULLIF(Relationship.value('(Name)[1]','nvarchar(250)'),'') AS RelName
,NULLIF(Relationship.value('(Description)[1]','nvarchar(MAX)'),'') AS RelDescription
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/PerformanceIndicator') AS XTbl(PerfInd)
CROSS APPLY PerfInd.nodes('Relationship') AS R(Relationship);
-- RELATIONSHIP REFERENT IDENTIFIER ==============================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlRelationshipRefId]
([RelIdentifier]
,[RelReferentIdentifier])
SELECT Rel.value('(Identifier)[1]','nvarchar(50)') AS RelIdentifier
,Rel.value('(ReferentIdentifier)[1]','nvarchar(50)') AS RelReferentIdentifier
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/PerformanceIndicator/Relationship') AS XTbl(Rel)
WHERE Rel.value('(ReferentIdentifier)[1]','nvarchar(50)') <> '';
-- PERFORMANCE MEASUREMENTS ======================================================================
WITH XMLNAMESPACES (DEFAULT 'urn:ISO:std:iso:17469:tech:xsd:PerformancePlanOrReport')
INSERT INTO [dbo].[SmlMeasurementInstance]
([PerfIndIdentifier]
,[PerfIndSequenceIndicator]
,[ResultType]
,[StartDate]
,[EndDate]
,[NumberOfUnits]
,[DescriptorName]
,[DescriptorValue]
,[Description])
SELECT MI.value('(../../Identifier)[1]','nvarchar(50)') AS PerfIndIdentifier
,MI.value('(../../SequenceIndicator)[1]','nvarchar(50)') AS PerfIndSequenceIndicator
,'Target'
,NULLIF(MI.value('(StartDate)[1]','date'),'') AS TargResStartDate
,NULLIF(MI.value('(EndDate)[1]','date'),'') AS TargResEndDate
,NULLIF(MI.value('(NumberOfUnitsTypeType)[1]','nvarchar(50)'),'') AS TargResDescriptorName
,NULLIF(MI.value('(Descriptor/DescriptorName)[1]','nvarchar(250)'),'') AS TargResDescriptorName
,NULLIF(MI.value('(Descriptor/DescriptorValue)[1]','nvarchar(250)'),'') AS TargResDescriptorValue
,NULLIF(MI.value('(Description)[1]','nvarchar(250)'),'') AS TargResDescription
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/PerformanceIndicator/MeasurementInstance/TargetResult') AS XTbl(MI)
UNION ALL
SELECT MI.value('(../../Identifier)[1]','nvarchar(50)') AS PerfIndIdentifier
,MI.value('(../../SequenceIndicator)[1]','nvarchar(50)') AS PerfIndSequenceIndicator
,'Actual'
,NULLIF(MI.value('(StartDate)[1]','date'),'') AS TargResStartDate
,NULLIF(MI.value('(EndDate)[1]','date'),'') AS TargResEndDate
,NULLIF(MI.value('(NumberOfUnitsTypeType)[1]','nvarchar(50)'),'') AS TargResDescriptorName
,NULLIF(MI.value('(Descriptor/DescriptorName)[1]','nvarchar(250)'),'') AS TargResDescriptorName
,NULLIF(MI.value('(Descriptor/DescriptorValue)[1]','nvarchar(250)'),'') AS TargResDescriptorValue
,NULLIF(MI.value('(Description)[1]','nvarchar(250)'),'') AS TargResDescription
FROM @StratML.nodes('/PerformancePlanOrReport/StrategicPlanCore/Goal/Objective/PerformanceIndicator/MeasurementInstance/ActualResult') AS XTbl(MI)
COMMIT TRANSACTION
SELECT @StrategicPlanName + ' loaded! ' AS StraML
RETURN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
-- Print error information.
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line: ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH;
END;
GO
Stored Procedure to Show the Strategic Plan
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20250428
-- Description: Return Strategic Plan values
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspSmlGetStrategicPlan]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StratPlan
TABLE (Item nvarchar(50)
,Content nvarchar(MAX));
INSERT INTO @StratPlan
exec dbo.uspSmlGetTableValue 'Plan','SmlStrategicPlan','StratPlanName';
INSERT INTO @StratPlan
exec dbo.uspSmlGetTableValue 'Organization','SmlOrganizations','OrgName';
INSERT INTO @StratPlan
exec dbo.uspSmlGetTableValue 'Vision','SmlStrategicPlanCore','Vision_Description';
INSERT INTO @StratPlan
exec dbo.uspSmlGetTableValue 'Mission','SmlStrategicPlanCore','Mission_Description';
INSERT INTO @StratPlan
SELECT 'Value: ' + [ValueName]
,[ValueDesc]
FROM [dbo].[SmlValues];
INSERT INTO @StratPlan
SELECT 'Goal ' + [GoalSequenceIndicator]
,[GoalDescription]
FROM [dbo].[SmlGoals];
SELECT Item
,Content
FROM @StratPlan;
END
GO
Stored Procedure to Retrieve a Table Value Stored Procedure
-- =============================================
-- Author: SCP - MSSQLTips
-- Create date: 20250428
-- Description: Return values from Table
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspSmlGetTableValue]
(@Title nvarchar(50)
,@Table nvarchar(50)
,@Column nvarchar(50)
,@Clause nvarchar(250) = NULL)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT @Title AS Title
,CAST(' + QUOTENAME(@Column) + ' AS nvarchar(MAX)) AS Value
FROM ' + QUOTENAME(@Table);
IF @Clause IS NOT NULL
SET @SQL += ' WHERE ' + @Clause;
SET @SQL += ';';
EXEC sp_executesql @SQL, N'@Title nvarchar(200)', @Title = @Title;
END
GOStored Procedure to Retrieve a Goal by its Sequence Id
-- =============================================
-- Author: SCP
-- Create date: 20250428
-- Description: Return Goal values By Sequence
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspSmlGetGoalBySeq]
(@SeqIndicator nvarchar(50))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @GoalId nvarchar(MAX);
SELECT @GoalId = [GoalIdentifier]
FROM [dbo].[SmlGoals]
WHERE [GoalSequenceIndicator] = @SeqIndicator;
IF @GoalId IS NULL
RETURN;
SET @SeqIndicator = 'GOAL ' + @SeqIndicator;
DECLARE @StratPlan
TABLE (Item nvarchar(MAX)
,Content nvarchar(MAX)
,ItemId int IDENTITY);
DECLARE @Clause nvarchar(MAX) = 'GoalIdentifier = ' + char(39) + @GoalId + char(39);
INSERT INTO @StratPlan
exec dbo.uspSmlGetTableValue @SeqIndicator,'SmlGoals','GoalDescription',@Clause;
INSERT INTO @StratPlan
VALUES ('STAKEHOLDER(S)','');
INSERT INTO @StratPlan
SELECT ' ' + [StakeholderType]
,[StakeholderName]
FROM [dbo].[SmlStakeholders]
WHERE [Identifier] = @GoalId AND
LEN([StakeholderName]) > 0;
INSERT INTO @StratPlan
VALUES ('OBJECTIVE(S)','');
WITH CteXml AS
(SELECT SUBSTRING([ObjectiveSequenceIndicator]
,PATINDEX('%[0-9]%', [ObjectiveSequenceIndicator])
,LEN([ObjectiveSequenceIndicator])) AS Tag
,[ObjectiveSequenceIndicator] AS Seq
,[ObjectiveName] AS Nme
,[ObjectiveDescription] AS Dsc
,[GoalIdentifier] AS GId
FROM [dbo].[SmlObjectives])
INSERT INTO @StratPlan
SELECT REPLICATE(' ',LEN(Tag)) + ' ' + Seq + ' - ' + Nme
,Dsc
FROM CteXml
WHERE GId = @GoalId
ORDER BY Tag;
SELECT Item
,Content
FROM @StratPlan;
END
GO
StratML Example 1
Importing a Strategic Plan
On this website, Strategy Markup Language (StratML), you can find some examples. I will use the NASA2022 Strategic Plan in this tip.
If you find any encoding problems, open the downloaded file in NOTEPAD and save it in ANSI format.
Change the folder path and file name, and run the following command:
EXEC [dbo].[uspStratMLImport] @XmlFilePathAndName = N'E:\<YOUR_FOLDER_NAME>\NASA2022.xml'
GOThe following message will appear:
Showing the Strategic Plan Core
EXECUTE [dbo].[uspSmlGetStrategicPlan]
GOResults:
Showing the Goal by its Sequence Number
EXECUTE [dbo].[uspSmlGetGoalBySeq] 2
GOResults:
StratML Example 2
Suppose that a Healthcare clinic has this Strategic Plan:
- Strategic Goal: Improve patient outcomes and access to preventive care.
- Objective: Increase the percentage of patients receiving annual wellness checkups.
- Performance Indicator (KPI): Percentage of patients who completed an annual wellness checkup visit in the past 12 months.
The Target value is 80 percent, and the Actual value is 76 percent, in the Measurement Instance for this KPI.
The Actual value can be updated every night from the Electronic Health Record (EHR) and pushed to the clinic management dashboard, which can also show weekly trends and triggers to alert management to investigate if the value reaches below a certain limit.
This illustrates the importance of using StratML tables to bring the Strategic plans, normally found in PDF documents, to the database, to centralize KPIs and give the benefit of real-time actions.
Next Steps
There are a lot of StratML features that were not covered in this tip but are described in the StratML XSD documentation, including Driving Forces, PESTLE Factors, SWOT, Level types, etc.
It is much easier to have a front-end to deal with inputs and outputs of the elements of StratML, including follow-up of measurements, which gives real-time progress as it happens, allowing data-driven decision making and gaining continuous improvement looping.
Exporting your data to StratML format is easier when using nested SELECT FOR XML clauses and following the XSD rules.
Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry. Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.
- MSSQLTips Awards
- Author of the Year – 2025
- Trendsetter (25+ tips) – 2025
- Rookie of the Year – 2024
