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
https://msdn.microsoft.com/en-us/library/ms178030.aspx
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 int
variable.
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)[1]', '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 [1]
is specified at the end of the path expression. For more information about static typing, see XQuery and Static Typing.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.