|
Execute the following
SQL Server T-SQL script in Management Studio Query Editor to demonstrate of XML data storage in local variables and tables with XML column type.
-- SQL Server select to XML variable - SQL select to xml document - SQL select to xml
USE AdventureWorks
GO
DECLARE @docXML XML
SET @docXML = (SELECT ShoppingCartID,
Quantity
FROM Sales.ShoppingCartItem
FOR XML AUTO, ROOT ('CartItems'), ELEMENTS)
SELECT @docXML
GO
/* Results
<CartItems>
<Sales.ShoppingCartItem>
<ShoppingCartID>14951</ShoppingCartID>
<Quantity>3</Quantity>
</Sales.ShoppingCartItem>
<Sales.ShoppingCartItem>
<ShoppingCartID>20621</ShoppingCartID>
<Quantity>4</Quantity>
</Sales.ShoppingCartItem>
<Sales.ShoppingCartItem>
<ShoppingCartID>20621</ShoppingCartID>
<Quantity>7</Quantity>
</Sales.ShoppingCartItem>
</CartItems>
*/
-- Category xml data
DECLARE @CAT XML;
SET @CAT = (SELECT ProductCategoryID, Name FROM Production.ProductCategory
FOR XML AUTO, TYPE)
SELECT @CAT
GO
/* Results
<Production.ProductCategory ProductCategoryID="4" Name="Accessories" />
<Production.ProductCategory ProductCategoryID="1" Name="Bikes" />
<Production.ProductCategory ProductCategoryID="3" Name="Clothing" />
<Production.ProductCategory ProductCategoryID="2" Name="Components" />
*/
-- Declare table variable with XML column type
DECLARE @Category TABLE (ID int, xml XML)
INSERT INTO @Category SELECT 1, ((SELECT ProductCategoryID, Name
FROM Production.ProductCategory FOR XML AUTO, TYPE))
SELECT * FROM @Category
GO
/* Results
ID xml
1 <Production.ProductCategory ProductCategoryID="4" Name="Accessories" /><Production.ProductCategory ProductCategoryID="1" Name="Bikes" /><Production.ProductCategory ProductCategoryID="3" Name="Clothing" /><Production.ProductCategory ProductCategoryID="2" Name="Components" />
*/ |
