01-27-2017 11:17 AM
Is there any built-in function/method in SAS-XML engine which is equivalent of "value() Method (xml Data Type)" of Transact SQL (Microsoft) ?
Details on "value() Method (xml Data Type)" of Transact SQL can be found in below link
Above method performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.
One can use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.
In the following example, an XML instance is stored in a variable of
xml type. The
value() method retrieves the
ProductID attribute value from the XML. The value is then assigned to an
DECLARE @myDoc xml DECLARE @ProdID int SET @myDoc = '<Root> <ProductDescription ProductID="1" ProductName="Road Bike"> <Features> <Warranty>1 year parts and labor</Warranty> <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> </Features> </ProductDescription> </Root>' SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)', 'int' ) SELECT @ProdID
Value 1 is returned as a result.
Although there is only one
ProductID attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the additional
 is specified at the end of the path expression. For more information about static typing, see XQuery and Static Typing.
01-28-2017 09:51 AM