DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 25

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

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.

SAS Super FREQ
Posts: 8,864

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

Posted in reply to nrk1787db1_gmail_com
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
Ask a Question
Discussion stats
  • 1 reply
  • 120 views
  • 1 like
  • 2 in conversation