XML functions

See XML Services for syntax parameters, examples, and usage information for these Transact-SQL functions that support XML in the database.

xmlextract

Applies an XML query expression to an XML document and returns the specified result. Information can be returned with or without the XML tags.

 xmlextract_expression ::=
	xmlextract (xml_query_expression,xml_data_expression
	[optional_parameters])
xml_query_expression ::=basic_string_expression 
xml_data_expression ::= general_string_expression
optional_parameters ::=
	options_parameter
	| returns_type
	| options_parameter returns_type
options_parameter ::= [,] option option_string 
returns_type ::= [,] returns dataype 
datatype ::= {string_type | computational_type | date_time_type }
string_type ::= char (integer) | varchar (integer) 
	| unichar (integer) | univarchar (integer)
	| text | unitext | image
computational_type ::= integer_type | decimal_type | real_type 
	| date_time_type
integer_type ::= [ unsigned ] {integer | int | tinyint | smallint | bigint}
decimal_type ::= {decimal | dec | numeric } [ (integer [, integer ] ) ]
real_type ::= real | float | double precision 
date_time_type ::= date | time | datetime
option_string ::= [,] basic_string_expression

xmlparse

Parses an XML document passed as a parameter, and returns an image (default), binary, or varbinary value that contains a parsed form of the document.

mlparse_call ::= 
	xmlparse(general_string_expression
	[options_parameter][returns_type])
options_parameter ::=  [,] option option_string
option_string ::= basic_string_expression
returns type ::= [,] returns {image | binary | varbinary [(integer )]}

xmlrepresentation

Examines the image parameter of an expression, and returns an integer value that indicates whether the parameter contains parsed XML data or another sort of image data.

xmlrepresentation_call::= 
	xmlrepresentation(parsed_xml_expression)

xmltable

Extracts data from an XML document and returns it as a SQL table.

xmltable_expression ::= xmltable
		( row_pattern passing xml_argument
		columns column_definitions 
		options_parameter)
	row_pattern ::= character_string_literal
	xml_argument ::=  
		xml_expression | column_reference | variable_reference
	column_definitions ::=
	column_definition [ { , column_definition } ]
			column_definition ::=
		ordinality_column | regular_column

ordinality_column ::= column_name datatype for ordinality
	regular_column ::=
		column_name datatype [ default literal ] [null | not null]
	[ path column_pattern ]
	column_pattern ::= character_string_literal
	options_parameter ::=[,] option option_string
	options_string ::= basic_string_expression

(Derived table syntax) Returns a SQL table from within a SQL from clause.

from_clause ::= from table_reference [, table_reference]...
table_reference ::= table_view_name | ANSI_join |derived_table  
table_view_name::= See the select command in Reference Manual 	
			Volume 2, "Commands."
ANSI_join::= See the select command in Reference Manual 
			Volume 2, "Commands."
derived_table ::=
		(subquery) as table_name [ (column_name [, column_name]...)| 
		xmltable_expression as table_name

xmltest

Is a SQL predicate that evaluates an XML query expression, which can reference the XML document parameter, and returns a Boolean result. xmltest resembles a SQL like predicate.

xmltest_predicate ::=
	xml_query_expression [not] xmltest xml_data
	[option option_string]
xml_data ::=
	xml_data_expression | (xml_data_expression)
xml_query_expression::= basic_string_expression
xml_data_expression ::= general_string_expression
option_string ::= basic_string_expression

xmlvalidate

Validates an XML document.

xmlvalidate_call ::=
	xmlvalidate ( general_string_expression, [optional_parameters])
optional_parameters ::= options_parameter 
	| returns_type
	|options_parameter returns type
options_parameter ::= [,] option option_string
options_string ::= basic_string_expression 
returns_type ::= [,] returns string_type
string_type ::=char (integer) | varchar (integer) 
	| unichar (integer) |univarchar (integer) 
	| text | unitext |image | java.lang.String