[SQL][MCSA]70-461 筆記 (三)

[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

image

 

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

image

 

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

image

 

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)

image