[筆記][SQL]FOR XML語法

2016-09-22

3375
0
2016-10-05

最近工作上需要將資料庫內的資料轉換成XML，稍微瞭解了一下MS SQL所提供的XML語法，做一些簡單的Lab，並記錄下來。

建立測試用Table及資料：首先建立一個暫時性的Table，作為Lab的資料來源。

CREATE TABLE #Orders
(
OrderUID INT NOT NULL IDENTITY,
Customer NVARCHAR( 100),
Item NVARCHAR( 200),
Quantity INT NOT NULL
)

INSERT INTO #Orders(Customer, Item, Quantity)
VALUES ('Foo', '可樂', 10)

INSERT INTO #Orders(Customer, Item, Quantity)
VALUES ('Boo', '餅乾', 20)

INSERT INTO #Orders(Customer, Item, Quantity)
VALUES ('Boo', '甜甜圈', 30)

FOR XML的模式

RAW模式

RAW模式會將查詢結果集的每一資料列轉成一個XML Element，並以row當作Element名稱，每個欄位則會以Attribute的形式顯示。

SQL Statement

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML RAW

結果會是

<row Customer="Foo" Item="可樂" Quantity="10" />
<row Customer="Boo" Item="餅乾" Quantity="20" />
<row Customer="Boo" Item="甜甜圈" Quantity="30" />

AUTO模式

將查詢結果以簡易巢狀 XML 樹狀結構傳回，會以Table名稱當作row Element的名稱

SQL Statement

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML AUTO

結果會是(因為是Temp Table，所以Table名稱會加上系統設定的前置詞)

<_x0023_Orders Customer="Foo" Item="可樂" Quantity="10" />
<_x0023_Orders Customer="Boo" Item="餅乾" Quantity="20" />
<_x0023_Orders Customer="Boo" Item="甜甜圈" Quantity="30" />

PATH模式

MSDN上的說明如下，看起來是可以較靈活且簡單的定義XML輸出格式：

提供更簡單的方式來混合元素與屬性，並引用其他的巢狀來代表複雜的屬性。您可以使用 EXPLICIT 模式查詢來建構從資料列集而來的這類 XML，但是 PATH 模式對於可能會比較繁雜的 EXPLICIT 模式查詢提供較簡單的替代方案。

SQL Statement

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML PATH

結果會是

<row>
  <Customer>Foo</Customer>
  <Item>可樂</Item>
  <Quantity>10</Quantity>
</row>
<row>
  <Customer>Boo</Customer>
  <Item>餅乾</Item>
  <Quantity>20</Quantity>
</row>
<row>
  <Customer>Boo</Customer>
  <Item>甜甜圈</Item>
  <Quantity>30</Quantity>
</row>

EXPLICIT模式

在MSDN的說明中，EXPLICIT模式可以最大幅度的控制XML產出形式。

RAW 和 AUTO 模式對從查詢結果產生之 XML 形式的控制不大。但是，EXPLICIT 模式在由查詢結果產生想要的 XML 方面，可提供的彈性最大。

只是也有許多語法細節要注意，所以就沒有進一步的研究下去。可以參考：搭配 FOR XML 使用 EXPLICIT 模式

指示詞

使用ELEMENTS指示詞

如果在句尾加上 ,ELEMENTS，則每個欄位都會以XML Element的形式產生。

SQL Statement:

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML RAW, ELEMENTS

結果會是：

<row>
  <Customer>Foo</Customer>
  <Item>可樂</Item>
  <Quantity>10</Quantity>
</row>
<row>
  <Customer>Boo</Customer>
  <Item>餅乾</Item>
  <Quantity>20</Quantity>
</row>
<row>
  <Customer>Boo</Customer>
  <Item>甜甜圈</Item>
  <Quantity>30</Quantity>
</row>

使用ROOT指示詞

使用ROOT指示詞，可以產生並指定所產出的XML的根標籤名稱。

以下SQL Statement會產生根標籤為Order的XML:

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML RAW, ROOT('Order')

產生的XML為

<Order>
  <row Customer="Foo" Item="可樂" Quantity="10" />
  <row Customer="Boo" Item="餅乾" Quantity="20" />
  <row Customer="Boo" Item="甜甜圈" Quantity="30" />
</Order>

使用XMLDATA 與 XMLSCHEMA

使用XMLDATA會回傳帶有結構描述的XML

SQL Statement:

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML RAW, XMLDATA

結果會是：

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="Customer" dt:type="string" />
    <AttributeType name="Item" dt:type="string" />
    <AttributeType name="Quantity" dt:type="i4" />
    <attribute type="Customer" />
    <attribute type="Item" />
    <attribute type="Quantity" />
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema1" Customer="Foo" Item="可樂" Quantity="10" />
<row xmlns="x-schema:#Schema1" Customer="Boo" Item="餅乾" Quantity="20" />
<row xmlns="x-schema:#Schema1" Customer="Boo" Item="甜甜圈" Quantity="30" />

如果要回傳的是XSD結構描述的XML格式，則使用XMLSCHEMA

SQL Statement:

SELECT Customer, Item, Quantity
FROM #Orders
FOR XML RAW, XMLSCHEMA

結果會是：

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="row">
    <xsd:complexType>
      <xsd:attribute name="Customer">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1028" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="100" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="Item">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1028" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="200" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="Quantity" type="sqltypes:int" use="required" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" Customer="Foo" Item="可樂" Quantity="10" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" Customer="Boo" Item="餅乾" Quantity="20" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" Customer="Boo" Item="甜甜圈" Quantity="30" />

參考

回首頁

攻城獅跳火圈

十八般武藝訓練中