SQL Server XML type

SQL Server supports XML types starting from 2005, which is useful for late changes. A one-to-many relationship turns into a many-to-many relationship at a later stage, and the XML type is a good choice.

　　1. Create test data

　　Create a table

　　-- CREATE table with XML Type column tableint, Info xml   

　　Inserting test data

 　　 --Insert 3 test dataINSERT personVALUES (1, ' <person><id>1</id><name> Liu Bei </name></person> ' ) insert person values (2, ' ) insert person values (3, " <person><id>3</id><name> Zhang Fei </name></person>< Span style= "color: #ff0000;" ')                 

　　2. Query node contents

Query () method

　　-- Querying node Content query () method SELECT id,info.query ('(/person/name) [1]2    

　　3. Query Value

Value () method

 　　 --Query values value () methodSELECT Id,info.value (‘(/person/name) [1] ",  varchar style= "COLOR: #ff0000;" > ') from person where ID  = 2 select * where Info.value ( '  (/person/name) [1]  ",  varchar style= "COLOR: #ff0000;" > ') =   Zhang Fei  '                 

　　4. Whether the query exists

exist () method

　　-- query whether there is a exist () method WHERE info.exist ('(/person/name) [1]1    

　　5. Query node

Nodes () method

--Query node SELECT T2. Loc.query ('.') ) from the person cross   APPLY info.nodes ('/person/name asT2 (LOC)    

　　6. Modify the XML

Modify () method

　　1, increase the node

　　--Modify (Insert) Add node SET info.modify (' insert <Age>25</Age> into (/person) [1]  ' ) 3       

　　2. Deleting nodes

　　-- Delete node contents (XQuery knowledge, no text () directly delete node) UPDATE person SET info.modify (' Delete (/person) [1]/age/t Ext ()' ) 3        

SQL Server XML type