SQL Server XML Shredding Part 3


XML Variable to Scalar Variable

A scalar variable, or scalar field, is a variable that holds one value at a time. It is a single component that assumes a range of number or string values. The xml data type is a built-in data type in SQL Server and is somewhat similar to other built-in types such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table as a variable type, a parameter type, a function-return type, or in CAST and CONVERT.

The value() method performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value. Here is an example from the Microsoft books online, that’s been modified only slightly.

DECLARE @myDoc xml  
DECLARE @ProdID int  
SET @myDoc = 
'<Root>  
  <ProductDescription ProductID="3296" 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

The result of this code is the value 3296. The value 3296 is part of the attribute ProductID. What if you needed the warranty information. How do you retrieve the string “1 year parts and labor”? Perhaps you intend to store that string into a table. Here below is the part of the code needed to get and display the string.

DECLARE @Warranty VARCHAR(100);
SET @Warranty =  @myDoc.value('(/Root/ProductDescription/Features/Warranty)[1]', 'varchar(100)' )  
SELECT @Warranty 
Series Navigation<< SQL Server XML Shredding Part 2SQL Server XML Shredding Part 4 >>

Leave a comment

Your email address will not be published. Required fields are marked *