MS SQL Server XML Integration Interview Questions And Answers

XML for Analysis facilitates communication of client applications with OLAP data sources. Communication is done via XML, HTTP, or SOAP. XMLA open standards support data access to data sources on the WWW.

Xquery is used to query XML data. This may be either a database or a file. It used xpath expressions that can be used to extract elements and attributes from XML documents.

XML indexes can either be primary or secondary. When the XML type column has its first index, it is called as a primary index. Using this primary index, secondary indexes like PATH, VALUE and PROPERTY are supported. Depending on the nature of queries, they help in query optimization.

Path secondary index if used speeds up queries if the query specifies a path.

Path secondary index if used speeds up queries are value based.

Path secondary index if used speeds up queries if the query retrieves one or more values from individual XML instances.

XML indexes are created on XML data type columns. Having XML indexes will reduce cost with respect to maintenance and data modification. Use of XML indexes avoids the need to parse the complete data at runtime thereby increasing query processing efficiency.

FOR XML allows data to be retrieved in an XML format. The FOR XML clause needs to be mentioned at the end of SELECT statement. There are three modes of FOR XML:-

RAW mode: - An XML element is formed for each row in the query results.

AUTO mode: - The query results are returned as nested XML elements.

Explicit mode: - the format of the XML document returned by the query can be completely controlled by the query.

OPENXML is a row set provider, in which a row set view over an XML documented is provided. It is used to parse the complex XML function.

Syntax:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]

Idoc is the document handle of an XML document which is created by calling sp_xml_preparedocument

Xpath is the pattern used to identify the nodes.

Flag is for mapping between the XML data and the relational rowset

WITH clause is used to provide a rowset format.

Stored procedures can be called using HTTP SOAP by creating endpoints. A SOAP endpoint is identified by a URL. Each endpoint supports a protocol, either HTTP OR TCP.

Syntax for Endpoint:

CREATE ENDPOINT name_of_end_point
STATE = STARTED
AS HTTP
(
PATH = specifies path of the service end point.
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = name of the host computer
)
FOR SOAP
(
WEBMETHOD – name of the web method to be used.
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = name of the database
NAMESPACE = specifies a name space for end point.
)