Extracts a specified property from a <msgheader> or <msgproperties> document. There are five msgpropvalue functions: msgproplist, msgpropcount, msgpropname, msgpropvalue, and msgproptype.
msgproplist_call::= msgproplist([ msg_doc] [returns varchar | text])) msgpropcount_call ::= msgpropcount([msg_doc]) msgpropname_call ::= msgpropname(integer[ ,msg_doc]), ) msgpropvalue_call::= msgpropvalue(prop_name [ , msg_doc] ) msgproptype_call::= msgproptype(prop_name [ , msg_doc] ) msg_doc ::= basic_character_expression prop_name::= basic_character_expression
Returns a string in the format of an option_string with all of the property attributes of msg_doc.
Returns the number of properties, or attributes, in msg_doc.
Returns the name of msg_doc’s Ith property, where I is the value of the integer parameter. The result is a null value if the value of the integer parameter is less than one or greater than the number of properties in msg_doc.
Returns the value for the msg_doc property whose name equals prop_name. The result is the property value converted to varchar, and is null if msg_doc does not have a property whose name is equal to prop_name.
Returns the message provider’s property type for the msg_doc property whose name equals prop_name. The result is null if msg_doc does not have a property whose name is equal to prop_name.
The <msgheader> or <msgproperties> XML document. A basic_character_expression. If msg_doc is not specified, the current value of @@msgprpoperties is used.
The property name from which you want to extract a value or type. A basic_character_expression.
The
following examples assume that a call from msgrecv returns
a message whose single property is named trade_name and
whose value is “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 ("Quick & Safe")’></msgproperties>
The ampersand and the quotation marks surrounding the phrase Quick
& Safe are replaced with the XML entities " and &,as
required by XML convention.
To retrieve 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.
To retrieve the list of properties belonging to a message, use one of the following examples:
select msgproplist()
select msgproplist(@@msgproperties)
The following query to msgpropvalue returns null because the message retrieved does not have a property named “discount”:
select msgpropvalue(’discount’, @@msgproperties)
To retrieve the number of properties from the last message retrieved:
select msgpropcount(@@msgproperties)
To retrieve the 8th property from the last message retrieved:
select msgpropname(8, @@msgproperties)
This query returns null, because the 9th property does not exist:
select msgpropname(9, @@msgproperties)
To retrieve the value of the 8th property:
select msgpropvalue (msgpropname(8, @@msgproperties))
If you omit the msg_doc parameter, the value of @@msgproperties is used.
If the result of the msgproplist call is more than 16K, the result value contains the words “TRUNCATED”. You should specify “RETURNS text” instead, in this case. You must use other msgprop functions to iterate through the property list and get 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)
This one is not:
declare @properties varchar (1000) select @properties= msgproplist(@@msgproperties returns varchar(1000))
| Copyright © 2004. Sybase Inc. All rights reserved. |
|
|
