[SQL][MCSA]70-461 筆記 (三)
-
Query and manage XML data.
- May include but not limited to: understand xml datatypes and their schemas and interop w/, limitations & restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export xml; xml indexing
從 SQL Server 2005 開始就有支援 XML 的資料型態和相關語法，利用此次準備的時候再次溫習一下，基本上 XML 的操作方法有五種：
- query() – 從 XML 欄位指定執行的 XQUERY ，取出 XML 類型的資料
- value() – 針對 XML 欄位取出一個純量值
- exist() – 判斷 XML 內某個 Node 是否存在，有則回傳 1 沒有就回傳 0
- modify() – 修改 XML 欄位內的資料
- node() – 將 XML Node 切分成多筆 Row
Query
declare @myDoc xml
set @myDoc = '<Root>
<ProductDescription ProductID="1" 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>'
SELECT @myDoc.query('/Root/ProductDescription/Features') as Result
Value
DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc = '<Root>
<ProductDescription ProductID="1" 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)[1]', 'int' )
SELECT @ProdID as Result
EXIST
declare @x xml
declare @f bit
set @x = '<root Somedate = "2002-01-01Z"/>'
set @f = @x.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2002-01-01Z")]')
select @f as Result
MODIFY 要配合 Update 指令使用
<從缺>
NODE
DECLARE @x xml
SET @x='<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
</Root>'
SELECT T.c.query('.') AS result
FROM @x.nodes('/Root/row') T(c)
