XML Data Type Methods(一)

/*XML Data Type Methods:

1.The query('XQuery') method retrieves(vt.检索,重新得到) a subset of untyped XML from the target XML instance

2.The value('XQuery',dataType) method returns a scalar value(标量值) from the targeted XML document.

    The returned value is converted to the data type you specify when you call the method.

3.The exist('XQuery') method lets you test for the existence of an element or one of its values

4.The nodes('XQuery') method returns what is essentially a table that includes one column.

    That means you should use the method only in those parts of a statement that can handle rowset views, such as the FROM clause.

    It also means that, when you call the nodes() method, you must assign a table alias and column alias to the rowset view returned by the method

5.The modify('XQuery') method lets you update that data

*/

DECLARE @StoresTable  TABLE

(

  StoreID INT IDENTITY(1,1) PRIMARY KEY,

  Survey_untyped XML,

  Survey_typed XML

)

 INSERT INTO @StoresTable(Survey_untyped,Survey_typed)

 VALUES

 (

  '<UnifiedRequest>

  <CommonInfo>

    <Version>1.14</Version>

    <Username>EC</Username>

  </CommonInfo>

  <OrderInfo>

    <CompanyCode>1003</CompanyCode>

    <PayTermsCode>024</PayTermsCode>

  </OrderInfo>

</UnifiedRequest>',

'<UnifiedRequest

   xmlns:i="http://www.w3.org/2001/XMLSchema-instance"

   xmlns="http://tempuri.org/UnifiedRequest.xsd">

  <CommonInfo>

    <Version>1.14</Version>

    <Username>EC</Username>

  </CommonInfo>

  <OrderInfo>

    <CompanyCode>1003</CompanyCode>

    <PayTermsCode>024</PayTermsCode>

  </OrderInfo>

</UnifiedRequest>'

)

--1.UntypedColumn

--1.1 query(xpathParameter) method

SELECT

  Survey_untyped.query('/UnifiedRequest/OrderInfo') AS Info_typed

FROM

  @StoresTable

/*Result:

<OrderInfo>

  <CompanyCode>1003</CompanyCode>

  <PayTermsCode>024</PayTermsCode>

</OrderInfo>

*/

--1.2 value(xpathParameter) method

SELECT

  Survey_untyped.value('(/UnifiedRequest/OrderInfo/CompanyCode/text())[1]','INT') AS CompanyCode

FROM

  @StoresTable

--1.3 exist(xpathParameter) method

SELECT TOP(1)

  CASE

    WHEN Survey_untyped.exist('/UnifiedRequest/OrderInfo/CompanyCode/text()')=1 THEN 'Found'

    ELSE 'Not Found'

  END

FROM

  @StoresTable

--1.4 nodes(xpathParameter) method

DECLARE @bikes XML

SET @bikes =

  '<Products>

    <Product>Mountain</Product>

    <Product>Road</Product>

   </Products>'

SELECT

  Category.query('./text()') AS BikeTypes1,--return a subset of xml

  Category.value('(./text())[1]','VARCHAR(20)') AS BikeTypes2--return string

FROM

  @bikes.nodes('/Products/Product')

    AS Bike(Category);

/*Result

    BikeTypes1 BikeTypes2

    ---------------------------

    Mountain    Mountain

    Road        Road

*/

--2.TypedColumn which contains namespace

--2.1 query(xpathParameter) method

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/UnifiedRequest.xsd')

SELECT

  Survey_typed.query('/UnifiedRequest/OrderInfo') AS Info_typed

FROM

  @StoresTable

/*Result:

<p1:OrderInfo xmlns:p1="http://tempuri.org/UnifiedRequest.xsd">

  <p1:CompanyCode>1003</p1:CompanyCode>

  <p1:PayTermsCode>024</p1:PayTermsCode>

</p1:OrderInfo>

*/

;WITH XMLNAMESPACES('http://tempuri.org/UnifiedRequest.xsd' AS UFD)

SELECT

  Survey_typed.query('/UFD:UnifiedRequest/UFD:OrderInfo') AS Info_typed

FROM

  @StoresTable

/*Result:

<UFD:OrderInfo xmlns:UFD="http://tempuri.org/UnifiedRequest.xsd">

  <UFD:CompanyCode>1003</UFD:CompanyCode>

  <UFD:PayTermsCode>024</UFD:PayTermsCode>

</UFD:OrderInfo>

*/

SELECT

  Survey_typed.query('declare namespace UFR="http://tempuri.org/UnifiedRequest.xsd";

    /UFR:UnifiedRequest/UFR:OrderInfo') AS Info_typed

FROM

  @StoresTable;

/*Result:

<UFR:OrderInfo xmlns:UFR="http://tempuri.org/UnifiedRequest.xsd">

  <UFR:CompanyCode>1003</UFR:CompanyCode>

  <UFR:PayTermsCode>024</UFR:PayTermsCode>

</UFR:OrderInfo>

*/

;WITH XMLNAMESPACES('http://tempuri.org/UnifiedRequest.xsd' AS UFD)

SELECT

  Survey_typed.query('/UFD:UnifiedRequest') AS Info_typed

FROM

  @StoresTable;

/*Result:

<UnifiedRequest xmlns="http://tempuri.org/UnifiedRequest.xsd" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

  <CommonInfo>

    <Version>1.14</Version>

    <Username>EC</Username>

  </CommonInfo>

  <OrderInfo>

    <CompanyCode>1003</CompanyCode>

    <PayTermsCode>024</PayTermsCode>

  </OrderInfo>

</UnifiedRequest>

*/

--2.2 value(xpathParameter,dataType) method

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/UnifiedRequest.xsd')

SELECT

  --Result:1003

  Survey_typed.value('(/UnifiedRequest/OrderInfo/CompanyCode/text())[1]','INT') AS CompanyCode

FROM

  @StoresTable

SELECT

  --Result:1003

  Survey_typed.value('declare namespace UFD="http://tempuri.org/UnifiedRequest.xsd";

(/UFD:UnifiedRequest/UFD:OrderInfo/UFD:CompanyCode/text())[1]','INT') AS CompanyCode

FROM

  @StoresTable

--2.3 exist(xpathParameter) method

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/UnifiedRequest.xsd')

SELECT TOP(1)

  CASE

    WHEN Survey_typed.exist('/UnifiedRequest/OrderInfo/CompanyCode/text()')=1 THEN 'Found'

    ELSE 'Not Found'

  END

FROM

  @StoresTable

--2.4 nodes(xpathParameter) method

DECLARE @bikes2 XML

SET @bikes2 =

  '<Products xmlns="http://tempuri.org/UnifiedRequest.xsd">

    <Product>Mountain</Product>

    <Product>Road</Product>

   </Products>'

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org/UnifiedRequest.xsd')

SELECT

  Category.query('./text()') AS BikeTypes1,--return a subset of xml

  Category.value('(./text())[1]','VARCHAR(20)') AS BikeTypes2--return string

FROM

  @bikes2.nodes('/Products/Product') AS Bike(Category);

/*Result

    BikeTypes1 BikeTypes2

    ---------------------------

    Mountain    Mountain

    Road        Road

*/