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.

Retrieves the message property trade_name:

select msgpropvalue('trade_name',@@msgproperties)
---------------
('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))

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF