재우니의 블로그





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)