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)', '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)', 'varchar(100)' ) SELECT @Warranty