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