SAS XML Engine - A function / method equivalent of value() Method (xml Data Type) of Transact SQL

nrk1787db1 — Fri, 27 Jan 2017 16:17:50 GMT

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.

A. Using the value() method against an xml type variable

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.

Re: SAS XML Engine - A function / method equivalent of value() Method (xml Data Type) of Transact SQ

Cynthia_sas — Sat, 28 Jan 2017 14:51:18 GMT

Hi:
Sort of sounds like it works like an XMLMap, when you use an XMLMap, you write instructions for how SAS can read an XML file into SAS format and populate the columns on each row with information from the XML tags. This paper has some good explanations and examples of importing an XML file into SAS using an XMLMap:
http://support.sas.com/resources/papers/proceedings12/253-2012.pdf

cynthia

topic Re: SAS XML Engine - A function / method equivalent of value() Method (xml Data Type) of Transact SQ in SAS Programming

SAS XML Engine - A function / method equivalent of value() Method (xml Data Type) of Transact SQL

A. Using the value() method against an xml type variable

Re: SAS XML Engine - A function / method equivalent of value() Method (xml Data Type) of Transact SQ