Node types and IDs for the SQL Server adapter

Metadata Node IDs

The Microsoft BizTalk Adapter for SQL Server surfaces SQL Server database artifacts in a hierarchical manner. The following table lists the node types and node IDs for SQL Server database artifacts that the SQL adapter surfaces. The node ID is the absolute path of the node that is used in the IMetadataRetrievalContractBrowse, Search, and GetMetadata methods.

Artifact Display Name	Node Type	Node ID	Example	Description
--	CATEGORY	/	/	WCF LOB Adapter SDK root node. Returns all first-level nodes; this includes the ExecuteNonQuery, ExecuteReader, and ExecuteScalar operation nodes and all schema nodes for the outbound operations, and the Polling operation node for the inbound operation.
ExecuteNonQuery	OUTBOUND OPERATION	GenericOp/ExecuteNonQuery	GenericOp/ExecuteNonQuery	ExecuteNonQuery operation node. Returns WSDL for the ExecuteNonQuery operation.
ExecuteReader	OUTBOUND OPERATION	GenericOp/ExecuteReader	GenericOp/ExecuteReader	ExecuteReader operation node. Returns WSDL for the ExecuteReader operation.
ExecuteScalar	OUTBOUND OPERATION	GenericOp/ExecuteScalar	GenericOp/ExecuteScalar	ExecuteScalar operation node. Returns WSDL for the ExecuteScalar operation.
Polling	INBOUND OPERATION	Polling	Polling	Polling operation node. Returns WSDL for the Polling operation.
Notification	INBOUND OPERATION	Notification	Notification	Notification operation node. Returns WSDL for the Notification operation.
Procedures	CATEGORY	Procedures/	Procedures/	Schema procedures node. Returns all procedures for the specified schema.
[DB_PROCEDURE]	OUTBOUND OPERATION	Procedure/[DB_SCHEMA]/[Procedure_Name]	Procedure/dbo/ADD_EMP_DETAILS	Procedure node. Returns the WSDL for the specified procedure.
Tables	CATEGORY	Tables/	Tables/	Schema tables node. Returns all table nodes for the specified schema.
[DB_TABLE]	CATEGORY	-	-	Table node. Returns all operation nodes (Insert, Select, Update, Delete, and Set) for the specified table. The Set operation is only returned for tables that contain columns with any of the following data type: Varchar(Max), Nvarchar(Max) or Varbinary(Max).
Insert	OUTBOUND OPERATION	TableOp/Insert/[DB_SCHEMA]/[DB_TABLE]	TableOp/Insert/dbo/Employee	Table Insert operation node. Returns WSDL for the Insert operation for the specified table.
Select	OUTBOUND OPERATION	TableOp/Select/[DB_SCHEMA]/[DB_TABLE]	TableOp/Select/dbo/Employee	Table Select operation node. Returns WSDL for the Select operation for the specified table.
Update	OUTBOUND OPERATION	TableOp/Update/[DB_SCHEMA]/[DB_TABLE]	TableOp/Update/dbo/Employee	Table Update operation node. Returns WSDL for the Update operation for the specified table.
Delete	OUTBOUND OPERATION	TableOp/Delete/[DB_SCHEMA]/[DB_TABLE]	TableOp/Delete/dbo/Employee	Table Delete operation node. Returns WSDL for the Delete operation for the specified table.
Set[COLUMN_NAME]	OUTBOUND OPERATION	TableOp/WriteText/[DB_SCHEMA]/[DB_TABLE]/[COLUMN_NAME]	TableOp/WriteText/dbo/Employee/Job_Description	Table Set operation node. Returns WSDL for the Set operation for the specified column in the table. (Only surfaced if the table contains columns with any of the following data type: (Max), Nvarchar(Max) or Varbinary(Max)).
Views	CATEGORY	Views/	Views/	Schema views node. Returns all view nodes for the specified schema.
[DB_VIEW]	CATEGORY	-	-	View node. Returns all operation nodes (Insert, Select, Update, and Delete) for the specified view.
Insert	OUTBOUND OPERATION	ViewOp/Insert/[DB_SCHEMA]/[DB_VIEW]	ViewOp/Insert/dbo/Employee_View	View Insert operation node. Returns WSDL for the Insert operation for the specified view.
Select	OUTBOUND OPERATION	ViewOp/Select/[DB_SCHEMA]/[DB_VIEW]	ViewOp/Select/dbo/Employee_View	View Select operation node. Returns WSDL for the Select operation for the specified view.
Update	OUTBOUND OPERATION	ViewOp/Update/[DB_SCHEMA]/[DB_VIEW]	ViewOp/Update/dbo/Employee_View	View Update operation node. Returns WSDL for the Update operation for the specified view.
Delete	OUTBOUND OPERATION	ViewOp/Delete/[DB_SCHEMA]/[DB_VIEW]	ViewOp/Delete/dbo/Employee_View	View Delete operation node. Returns WSDL for the Delete operation for the specified view.
Scalar Functions	CATEGORY	ScalarFunctions/	ScalarFunctions/	Schema scalar functions node. Returns all scalar functions for the specified schema.
[DB_SCLR_FUNCTION]	OUTBOUND OPERATION	ScalarFunction/[DB_SCHEMA]/[DB_SCLR_FUNCTION]	ScalarFunction/dbo/GET_EMP_ID	Scalar function node. Returns the WSDL for the specified scalar function.
Table Valued Functions	CATEGORY	TableFunctions/	TableFunctions/	Schema table valued functions node. Returns all table valued functions for the specified schema.
[DB_TBL_FUNCTION]	OUTBOUND OPERATION	TableFunction/[DB_SCHEMA]/[DB_TBL_FUNCTION]	TableFunction/dbo/TVF_EMPLOYEE	Table valued function node. Returns the WSDL for the specified table valued function.

[DB_SCHEMA] = Collection of SQL Server artifacts; for example, dbo.

[DB_TABLE] = The name of an SQL Server table; for example, Employee.

[DB_VIEW] = The name of an SQL Server view; for example, Employee_View.

[DB_PROCEDURE] = The name of an SQL Server stored procedure; for example, ADD_EMP_DETAILS.

[DB_SCLR_FUNCTION] = The name of an SQL Server scalar function; for example, GET_EMP_ID.

[DB_TBL_FUNCTION] = The name of an SQL Server table valued function; for example, TVF_EMPLOYEE.

Metadata Search and Node IDs

Metadata search is a powerful feature that the WCF LOB Adapter SDK surfaces as part of its MetadataRetrievalContract interface. The SQL adapter uses this feature to support searching on the following SQL Server artifacts. The metadata search scope is restricted to the level immediately under the node at which the search operation is performed. For example, to search for a scalar function, you must be searching under /Scalar Function/[Schema]. Recursive search is not supported.

Artifact	Node ID	Node Type Returned	Description
/ (i.e. Root node)	/	CATEGORY	Return all schema nodes that match the search expression.
[DB_PROCEDURE]	/Procedure/[DB_SCHEMA]	OUTBOUND OPERATION	Return all procedure nodes in the specified schema that match the search expression.
[DB_TABLE]	/Table/[DB_SCHEMA]	CATEGORY	Return all table nodes in the specified schema that match the search expression.
[DB_VIEW]	/View/[DB_SCHEMA]	CATEGORY	Return all view nodes in the specified schema that match the search expression.
[DB_SCLR_FUNCTION]	/ScalarFunction/[DB_SCHEMA]	OUTBOUND OPERATION	Return all scalar function nodes in the specified schema that match the search expression.
[DB_TBL_FUNCTION]	/TableFunction/[DB_SCHEMA]	OUTBOUND OPERATION	Return all table valued function nodes in the specified schema that match the search expression.

[DB_SCHEMA] = Collection of SQL Server artifacts; for example, dbo.

[DB_TABLE] = The name of an SQL Server table; for example, Employee.

[DB_VIEW] = The name of an SQL Server view; for example, Employee_View.

[DB_PROCEDURE] = The name of an SQL Server procedure; for example, ADD_EMP_DETAILS.

[DB_SCLR_FUNCTION] = The name of an SQL Server scalar function; for example, GET_EMP_ID.

[DB_TBL_FUNCTION] = The name of an SQL Server table valued function; for example, TVF_EMPLOYEE.

You can specify search expressions that are compatible with any valid expression that can be used for the SQL Server LIKE operator. For example, to perform a search on the tables contained in a schema, the SQL adapter executes the following SQL: SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE ‘[SEARCH_STR]’.

The following table lists the special characters that the SQL adapter supports in search expressions.

Special Character	Interpretation
% (percentage)	Matches zero or more characters. For example, "A%" matches "A", "AB", "ABC", and so on.
_ (underscore)	Matches exactly 1 character. For example, "A_" matches "AB", "AC", "AD", and so on.
[ ]	- Escapes the special meaning of _ and %. - Specifies a range or set of characters to be present. For example: - %[%]% matches all names that include a % symbol. - [a-f] matches all names that have characters between and including ‘a’ and ‘f’. - [abc] matches all names that have characters ‘a’, ‘b’, and ‘c’.
[^]	Specifies a range or set of characters not to be present. For example: - [^a-f] matches all names that do not have characters between and including ‘a’ and ‘f’. - [^abc] matches all names that do not have characters ‘a’, ‘b’, and ‘c’.

Metadata Retrieval and Node IDs

The following table summarizes the metadata characteristics returned by SQL adapter.

Artifact	Metadata Characteristics
Table or View	Table name. Table field names. Table field data types are mapped to simple or complex WSDL types. Table field length is mapped to facet maxLength. Table field primary key constraint is mapped to facet minOccurs = 1. Table field NULL constraint is mapped to facet isNillable = true. Table operations INSERT SELECT UPDATE DELETE SET<Column Name>
Procedure or Function	- Procedure or function name is mapped to the operation name. - Procedure or function parameter names. - Procedure or function parameter data types are mapped to WSDL types. - Procedure or function parameter direction is mapped to WSDL parameter direction. - Procedure parameter or function parameter data type length is mapped to facet maxLength. - Procedure or function parameter order is mapped to element sequence. - Function return data type is mapped to WSDL type. - Function return data type length is mapped to facet maxLength.

For detailed information about the format of the metadata that the SQL adapter exposes for specific artifacts and operations on the SQL Server database, see Messages and Message Schemas for BizTalk Adapter for SQL Server.