如何在SQL Server中查询xml数据？ SQL Server对xml数据类型的支持

<font>
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDetails XML
);
</font>

<font>
INSERT INTO Orders (OrderID, OrderDetails)
VALUES (1, '
<Order>
  <Customer>张三</Customer>
  <Items>
    <Item Product="手机" Quantity="1" Price="5999" />
    <Item Product="耳机" Quantity="2" Price="199" />
  </Items>
  <Total>6397</Total>
</Order>');
</font>

<font>
-- 获取 Items 节点下的所有 Item 元素
SELECT OrderDetails.query('/Order/Items/Item') AS Items
FROM Orders
WHERE OrderID = 1;
</font>

<font>
-- 提取客户姓名
SELECT OrderDetails.value('(/Order/Customer)[1]', 'NVARCHAR(50)') AS CustomerName
FROM Orders
WHERE OrderID = 1;
<p>-- 提取第一个商品名称
SELECT OrderDetails.value('(/Order/Items/Item[1]/@Product)[1]', 'NVARCHAR(100)') AS FirstProduct
FROM Orders;
</font>

<font>
-- 将每个 Item 转换为一行数据
SELECT 
    x.Item.value('@Product', 'NVARCHAR(100)') AS Product,
    x.Item.value('@Quantity', 'INT') AS Quantity,
    x.Item.value('@Price', 'DECIMAL(10,2)') AS Price
FROM Orders
CROSS APPLY OrderDetails.nodes('/Order/Items/Item') AS x(Item);
</font>

<font>
-- 查找是否包含“手机”的订单
SELECT OrderID
FROM Orders
WHERE OrderDetails.exist('/Order/Items/Item[@Product="手机"]') = 1;
</font>

<font>
UPDATE Orders
SET OrderDetails.modify('
  insert <Item Product="充电器" Quantity="1" Price="99" />
  as last into (/Order/Items)[1]
')
WHERE OrderID = 1;
</font>

<font>
UPDATE Orders
SET OrderDetails.modify('
  delete /Order/Items/Item[@Product="耳机"]
')
WHERE OrderID = 1;
</font>

<font>
CREATE PRIMARY XML INDEX IX_XML_OrderDetails
ON Orders (OrderDetails);
</font>

<font>
CREATE XML INDEX IX_XML_OrderDetails_PATH
ON Orders (OrderDetails)
USING XML INDEX IX_XML_OrderDetails FOR PATH;
</font>