SQL Server XML Data (FOR XML / OPENXML)

2021-11-27

筆記 SQL Server 如何使用 FOR XML 來將資料轉成 XML 資料形式以及如何使用 OPENXML 將 XML 資料讀入 SQL Server 轉為資料表的形式。

FOR XML

USE AdeventureWorks

SQL Data to XML Format

FOR XML RAW

USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW

SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW('Product'), ELEMENTS

FOR XML AUTO

SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO

FOR XML PATH

SELECT ProductID AS "@ProductID",
       Name AS "*",
       Size AS "Description/@Size",
       Color AS "Description/text()"
FROM Production.Product
ORDER BY Name
FOR XML PATH('Product')

OPENXML

Read XML To SQL Server

Setup

DECLARE @doc xml
SET @doc = '<?xml version="1.0" ?>
  <SalesInvoice InvoiceID="1000" CustomerID="123" OrderDate="2004-03-07">
    <Items>
      <Item ProductCode="12" Quantity="2" UnitPrice="12.99">
        <ProductName>Bike</ProductName>
      </Item>
      <Item ProductCode="41" Quantity="1" UnitPrice="17.45">
        <ProductName>Helmet</ProductName>
      </Item>
      <Item ProductCode="2" Quantity="1" UnitPrice="2.99">
        <ProductName>Water Bottle</ProductName>
      </Item>
    </Items>
  </SalesInvoice>'

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc

Read Attribute or Element

SELECT * FROM
OPENXML(@docHandle, '/SalesInvoice/Items/Item', 3)
WITH
(	ProductCode	int,
	Quantity	int,
	UnitPrice	float,
	ProductName nvarchar(20))

SELECT * FROM
OPENXML(@docHandle, '/SalesInvoice/Items/Item', 1)
WITH
(	InvoiceID	int '../../@InvoiceID',
	CustomerID	int '../../@CustomerID',
	OrderDate	datetime '../../@OrderDate',
	ProductCode	int,
	Quantity	int,
	UnitPrice	float,
	ProductName nvarchar(20) './ProductName')

EXEC sp_xml_removedocument @docHandle
GO

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記