MSSQL 의 XML TYPE 내부의 함수 알아보기

http://www.c-sharpcorner.com/uploadfile/mscratnesh/xml-data-type-in-microsoft-sql-server-2005/

CREATE TABLE Customers

                   (

                             [CustomerID] [int] IDENTITY(1,1) NOT NULL,

                             [CustomerName] [varchar](64) NULL,

                             [AddressXML] [xml] NULL

                   )

go

-- insert

truncate table Customers

insert into Customers values ('TestCustomer',

'<CustomerAddress Id="1">

  <line1>abc1</line1>

  <line2>xyz1</line2>

  <line3>1234561</line3>

</CustomerAddress>

<CustomerAddress Id="2">

  <line1>abc2</line1>

  <line2>xyz3</line2>

  <line3>1234563</line3>

</CustomerAddress>

')

-- query()

SELECT AddressXML.query ('/CustomerAddress[@Id = 1]')  

FROM   Customers

-- Value()

SELECT  * FROM Customers

WHERE AddressXML.value('(/CustomerAddress/line1)[1]', 'nvarchar(1000)')  ='abc1'

-- exist()

SELECT  * FROM Customers 

WHERE  AddressXML.exist ('/CustomerAddress[@Id = 1]') = 1

-- Modify()

UPDATE Customers SET AddressXML.modify('

  insert    <Line4>Newline</Line4>

  after (/CustomerAddress/line3)[1]')

-- 첫번째(1) 배열의 id 열에 값을 100 으로 변경

UPDATE Customers SET AddressXML.modify('

  replace value of (/CustomerAddress/@Id)[1] with 1' )

-- nodes(), 공통 xml 

SELECT AddressXML.query('.')as nodes

FROM   Customers

CROSS APPLY AddressXML.nodes('/CustomerAddress') as MyNodes(a)

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)

SELECT T.c.query('..') AS result

FROM   @x.nodes('/Root/row') T(c)