msgpropvalue

Description

Extracts and returns from a <msgheader> and <msgproperties> document the value for the msg_doc property where the name equals prop_name. The result is the property value converted to varchar, and is a null value if msg_doc does not have a property with name that is equal to prop_name.

Syntax

msgpropvalue_call ::= msgpropvalue(prop_name [ , msg_doc] )
	msg_doc ::= basic_character_expression
	prop_name::= basic_character_expression

Parameters

msgpropvalue_call: makes the request to use the msgpropvalue function.

msg_doc: is the <msgheader> or <msgproperties> XML document. A basic_character_expression. If msg_doc is not specified, the current value of @@msgprpoperties is used.

prop_name: is the property name from which you want to extract a value or type. A basic_character_expression.

Examples

Example 1

These examples assume that a call from msgrecv returns a message with a single property named “trade_name” and value of “Acme Maintenance” (“Quick & Safe”). The value of the @@msgproperties global variable is then:

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
     <msgproperties
             trade_name='Acme Maintenance (&quot;Quick &amp; Safe&quot;)'>
     </msgproperties>

The ampersand and the quotation marks surrounding the phrase Quick & Safe are replaced with the XML entities " and &, as required by XML convention. The following retrieves the message property trade_name:

select msgpropvalue(@@msgproperties, 'trade_name')
---------------
('Quick & Safe') Acme Maintenance

This is the original string that is stored in an Transact-SQL variable or column.

Example 2

Returns a null value because the message retrieved does not have a property named “discount”:

select msgpropvalue('discount', @@msgproperties)

Example 3

Retrieves the value of the eighth property:

select msgpropvalue (msgpropname(8, @@msgproperties))