Assert verifies that index is the XML index. Fail when index has not been found for a specified table and when index is not an XML index.

Note:
- Supported in SQL Server framework versions.
When v_IndexType value is NULL or empty, assert will not check the type of the XML index, however it will verify that index is the XML index of any type

Arguments

  • v_IndexName – index name, SYSNAME
  • v_ObjectName – table or view name, SYSNAME
  • v_IndexType - expected index type, NVARCHAR(50):
         - PRIMARY_XML
         - SECONDARY_XML_VALUE
         - SECONDARY_XML_PATH
         - SECONDARY_XML_PROPERTY
  • v_UserMessage – message to report when assertion fails, NVARCHAR(MAX)

Examples

 
SQL Server 

Below is assert that verifies index created in the following statement:

CREATE TABLE IndexTable(
   DocID int NOT NULL,
   DocName VARCHAR(255),
   XMLData XML);
GO

CREATE NONCLUSTERED INDEX IX_INDEXTABLE_on_DocName
    ON INDEXTABLE (DocName);
GO

ALTER TABLE INDEXTABLE
ADD CONSTRAINT PK_INDEXTABLE_on_TempID
PRIMARY KEY CLUSTERED (DocID);
GO

--create Primary XML index
CREATE PRIMARY XML INDEX PXML_INDEXTABLE_on_XMLData
    ON dbo.INDEXTABLE (XMLData)
GO

--Creates a secondary XML index on columns built on path values
--and node values in the primary XML index.
--In the PATH secondary index, the path and node values are
--key columns that allow efficient seeks
--when searching for paths.
CREATE XML INDEX IXML_INDEXTABLE_on_XMLData_for_Path
    ON dbo.INDEXTABLE (XMLData)
    USING XML INDEX PXML_INDEXTABLE_on_XMLData FOR PATH ;
GO

--Creates a secondary XML index on columns where key columns are
--(node value and path) of the primary XML index.
CREATE XML INDEX IXML_INDEXTABLE_on_XMLData_for_Value
    ON dbo.INDEXTABLE (XMLData)
    USING XML INDEX PXML_INDEXTABLE_on_XMLData FOR VALUE;
GO

--Creates a secondary XML index on columns (PK, path and
--node value) of the primary XML index
--where PK is the primary key of the base table.
CREATE XML INDEX IXML_INDEXTABLE_on_XMLData_for_Property
    ON dbo.INDEXTABLE (XMLData)
    USING XML INDEX PXML_INDEXTABLE_on_XMLData FOR PROPERTY;
GO

--now lets populate some test data in to the table
DECLARE @XMLData XML =
'<dataset>
  <metadata>
   <item name="CarModel" type="xs:string" length="100" />
   <item name="CarMake" type="xs:string" length="100" />
  </metadata>
  <data>
  <row>
   <value>135i</value>
   <value>BMW</value>
  </row>
   <row>
    <value>150</value>
    <value>Ford</value>
   </row>
  </data>
</dataset>'

INSERT INTO IndexTable (DocID, DocName, XMLData)
VALUES( 1, 'Test XML Document', @XMLData)

GO;

EXEC DBTD_ASSERT_INDEX_XML 'PXML_INDEXTABLE_on_XMLData', 'IndexTable', 
       'PRIMARY_XML', 'Issues with XML index';
EXEC DBTD_ASSERT_INDEX_XML 'IXML_INDEXTABLE_on_XMLData_for_Path', 'IndexTable', 
       'SECONDARY_XML_PATH', 'Issues with XML index';
EXEC DBTD_ASSERT_INDEX_XML 'IXML_INDEXTABLE_on_XMLData_for_Value', 'IndexTable', 
       'SECONDARY_XML_VALUE', 'Issues with XML index';
EXEC DBTD_ASSERT_INDEX_XML 'IXML_INDEXTABLE_on_XMLData_for_Property', 'IndexTable', 
       'SECONDARY_XML_PROPERTY', 'Issues with XML index';
 

See Also