msgproplist

Description

Extracts and returns from a <msgheader> and <msgproperties> document a string in the format of an option_string with all of the property attributes of msg_doc.

Syntax

msgproplist_call::= msgproplist([ msg_doc] [returns varchar | text]))
	msg_doc ::= basic_character_expression
	prop_name::= basic_character_expression

Parameters

msgproplist_call: makes the request to use the msgproplist 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.

returns varchar | text: specifies the format of the returning message.

Examples

Example 1

This example assumes 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.

Either of these retrieves the list of properties belonging to a message:

select msgproplist

select msgproplist(@@msgproperties)

Usage

If the result of the msgproplist call is more than 16K, the result value contains the word “TRUNCATED”. If this happens, specify returns text so that the results are not truncated. You must use other msgprop functions to iterate through the property list and obtain the names and values of the properties.
If you run msgproplist without a return length, any output over the default return value (32) is truncated. To avoid this, specify the length of your returns. For example, this statement is truncated:
```
declare @properties varchar(1000)
select @properties = msgproplist(@@msgproperties returns varchar)
```
However, this one is not:
```
declare @properties varchar (1000)
select @properties= msgproplist(@@msgproperties returns varchar(1000))
```