SQL Server XML data type DML-related operations (graphic combination)

XML operations are seldom used, recently query a statement, but also get the official website view usage. So according to the official to the relevant DML to operate once, as a record! ~

--Test data--DROP table #XMLTab CREATE table #XMLTab (colxml XML) GO INSERT into #XMLTab select N ' <record dt= "2015 -05-05 "> <exception type=" ring_buffer_exception "> <task address=" 0x0062b8e8 "/> <Error>9 003</error> <Severity>error</Severity> </Exception> <exception type= "Buffer_exception" > <task address= "0xc452bb39"/> <Error>8008</Error> <severity>true</severity&    Gt <Other>00</Other> </Exception> </Record> ' GO INSERT into #XMLTab SELECT N ' <p1:record dt= "2015-05-06" xmlns:p1= "Http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" > <p1:exception type= "ring_buffer_exception" > <p1:task address= "0x0062b8e8"/> <p1:Error&gt ;9003</p1:error> <p1:Severity>error</p1:Severity> </p1:Exception> <p1:exception type= " Buffer_exception "> &LT;p1:task address= "0xc452bb39"/> <p1:Error>8008</p1:Error> <p1:severity>true</p1:severi  ty> <p1:Other>00</p1:Other> </p1:Exception> </p1:Record> ' GO

' XML Data modification language (XML DML) '

-- node Insertion

Insert Expression1 ({as First| as Last } into| After | before Expression2 )

-- Delete XML the node of the instance          

Delete Expression

-- Update the value of a node in a document

Replace value of Expression1 with Expression2

--In the root path "/record/", under the first "/exception", add to delete an element "<Address>". You can also use {as First | as last} to specify the location of the update #XMLTab SET Colxml.modify (' Insert <address>insert test 1</address> into (/record/exception) [1] '); UPDATE #XMLTab SET colxml.modify (' Insert <address>insert test 1</address> as first into (/record/exception) [ 1];--Delete all elements "Address" UPDATE #XMLTab SET colxml.modify (' delete/record/exception[1]/address '); UPDATE #XMLTab SET colxml.modify (' delete/record/exception[1]/address[1] ');

--In the root path "/record/", under the second "/exception", add delete an element "<Address>" UPDATE #XMLTab SET colxml.modify (' Insert <address >insert test 1</address> into (/record/exception) [2];--Delete all elements under the second node "/exception" the "Address" UPDATE #XMLTab SET colxml.modify (' delete/record/exception[2]/address '); UPDATE #XMLTab SET colxml.modify (' delete/record/exception[2]/address[1] ');

--Delete the 2nd element under "/record/exception/" (that is, the deletion of the second row in Exception) UPDATE #XMLTab SET colxml.modify (' delete/record/exception/*[2] ')

--Delete all the elements under "/record/exception" in the Path "<Address>" UPDATE #XMLTab SET colxml.modify (' delete/record/exception/address [1] '); UPDATE #XMLTab SET colxml.modify (' delete/record/exception/address ');

Various path expressions specify "[1]" or "[2]" and so on, requiring only a single target to be returned at a time.

Create a test table in a re-delete

--Insert an element variable (executed 4 times for easy testing) DECLARE @newFeatures XML; Set @newFeatures = N ' <Address></Address> ' UPDATE #XMLTab SET colxml.modify (' Insert sql:variable ("@ NewFeatures ") into (/record/exception) [1] ');

--Insert an attribute into the element "<address/>" (Next step) (multiple elements same, address[1]/[2]/[3]/[4]) UPDATE #XMLTab SET colxml.modify (' Insert Attribute Attrname {"Attributea"} into (/record/exception/address[1]) [1] '); UPDATE #XMLTab SET colxml.modify (' Insert attribute attrname {"Attributeb"} into (/record/exception/address[2]) [1] '); UPDATE #XMLTab SET colxml.modify (' Insert attribute attrname {"Attributea"} into (/record/exception/address[3]) [1] '); UPDATE #XMLTab SET colxml.modify (' Insert attribute attrname {"Attributeb"} into (/record/exception/address[4]) [1] ');

--Insert an attribute into the element "<address/>", where the condition is [attrname= "Attributeb"], (each element is distinguished by address[1]/[2]/[3]/[4]) UPDATE #XMLTab SET Colxml.modify (' Insert attribute attrName2 {"0.5"} into (/record/exception/address[4][@attrName = "Attributeb"]) [1] '); --Success: Fourth Address,attrname= "Attributeb" UPDATE #XMLTab SET colxml.modify (' Insert attribute AttrName3 {"0.5"} into (/ record/exception/address[4][@attrName = "Attributea"]) [1] ');--Failure: Fourth address,attrname<> "Attributea"

--Delete the attribute "Attrname" (all) of the "Address" in the Path "/record/exception/address" (All) UPDATE #XMLTab SET colxml.modify (' delete/record/ exception/address/@attrName '); UPDATE #XMLTab SET colxml.modify (' delete/record/exception/address/@attrName2 ');

--Variable Insert property value declare @Hrs FLOAT        set @Hrs =0.5update #XMLTab SET colxml.modify (' Insert attribute attrname {sql:variable (" @Hrs ")} into (/record/exception/task) [1] ');

--Insert Multiple property values Update #XMLTab SET colxml.modify (' Insert (attribute attrName2 {"0.5"},attribute attrName3 {". 2"}  ) into (/ Record/exception/task) [1];

--Insert annotation element (insert after 3rd address) UPDATE #XMLTab SET colxml.modify (' Insert <!--some comment--and after (/record/exception/ ADDRESS[3]) [1];

--using "before" to insert processing instructions in front of the directory (header) Update #XMLTab SET colxml.modify (' Insert < program = "Instructions.exe"?> before (/record) [1];

--You can also use "after" and "before" methods to insert an element update #XMLTab SET colxml.modify (' Insert <Address></Address> after (/ RECORD/EXCEPTION/ADDRESS[1]) [1]; UPDATE #XMLTab SET colxml.modify (' Insert <Address></Address> before (/record/exception/address[1]) [1] ');

--Add delete text (after adding text, the element format is displayed in one line) UPDATE #XMLTab SET colxml.modify (' Insert text{' Product Catalog Description} as first into (/ Record/exception) [1];--View after deletion, normal format update #XMLTab SET colxml.modify (' Delete/record/exception/text () ');--The following format is added

--Add property by condition Change:--if the property of "/record/exception/address[3]" attrname= "attributeb", increase the property attrname= "10", otherwise increase the property attrname= "50" UPDATE #XMLTab SET colxml.modify (' insertif (/record/exception/address[3][@attrName = "Attributeb") then attribute attrname {"}else" attribute attrname {"Ten"}as first into (/record/exception/address[3]) [1];

--Change the text value "9003" in Element "<Error>9003</Error>" to "10000" () UPDATE #XMLTab SET colxml.modify (' Replace value of Record/exception/error[1]/text ()) [1] with "10000");--Change the value of the attribute "address" of the element "Task" to "" "UPDATE #XMLTab SET Colxml.modify (' Replace value ' of (/record/exception/task/@address) [1] with "100" '); UPDATE #XMLTab SET colxml.modify (' Replace value of (/record/exception/task[1]/@address) [2] with "999" ');

--If the number of element address is greater than 3 in the first exception under the/record/path, the update Exception[1] property type is "10.0" or "20.0" Update #XMLTab SET colxml.modify ('  replace value of (/record/exception[1]/@type) [1] with  (       if (count (/record/exception[1]/address) > 3 ) then "10.0"       Else "20.0"  );

-------------------------------------------------------------------------------------
The following is an XML-related query
-------------------------------------------------------------------------------------

--Query all subkeys in an element select Colxml.query ('/record/exception ') from #XMLTabSELECT colxml.query ('//record/exception/ Address ') from #XMLTab--the result of a second query statement

--element prefixed query. The second row of data that was previously inserted in the staging table is now useful for!~select colxml.query (' Declare namespace p1= ' http://schemas.microsoft.com/ Sqlserver/2004/07/adventure-works/productmodeldescription ";/p1:record/p1:exception ') from #XMLTab

--For conditional queries, if the true () or false () function is specified in the exist () method, always returns the 1SELECT colxml from #XMLTab WHERE colxml.exist (' true () ') =1select COLXM L from #XMLTab WHERE colxml.exist (' false () ') =1

--Query method with prefix Select Colxml, Colxml.exist (' Declare namespace p1= ' http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ Productmodeldescription ";/p1:record/p1:exception/p1:error[(Text () [1] cast as xs:string?) = Xs:string (" 9003 ")] '), Colxml.exist (' Declare namespace p1= ' http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ Productmodeldescription ";/p1:record/p1:exception/p1:error[(Text () [1] cast as xs:string?) = Xs:string (" 8008 ")] '), Colxml.exist (' Declare namespace p1= ' http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ Productmodeldescription ";/p1:record/p1:exception[1]/p1:error[(Text () [1] cast as xs:string?) = Xs:string (" 9003 ")] '), Colxml.exist (' Declare namespace p1= ' http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ Productmodeldescription ";/p1:record/p1:exception[2]/p1:error[(Text () [1] cast as xs:string?) = Xs:string (" 8008 ")] ') From #XMLTab 
 

-Element judgment: Whether other elements in the element exist. 1 is not included in the node. SELECT colxml, Colxml.exist ('/record/exception[1][not (Other) ')--1: Element "Exception [1] "does not contain the element" other ", Colxml.exist ('/record/exception[2][not (Other)] ')--0: The Element" Exception[2 "contains the element" other ", Colxml.exist ( ' Declare namespace p1= ' Http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription '; P1:record/p1:exception[1][not (P1:other)]  --1: Element "p1:exception[1" does not contain element "P1:other", Colxml.exist (' Declare namespace p1= "Http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";/P1: Record/p1:exception[2][not (P1:other)]  

--use "value" to query property values and element literal values Select  colxml.value (' (/record/exception/task/@address) [1] ', ' varchar ') as [ Address1],colxml.value (' (/record/exception[2]/task/@address) [1] ', ' varchar ') as [Address2],colxml.value (' Data (/record/exception[1]/error) [1] ', ' int ') as [Error1], Colxml.value (' Data (/record/exception[2]/error) [1] ', ' int ' ) as [Error2]  

--Query property value using "Query" SELECT    Convert (NVARCHAR), Colxml.query (' Data (/record/@dt) ') as [id]  , CONVERT ( NVARCHAR (+), Colxml.query (' Data (/record/exception/@type) [1] ')) as [Type1], CONVERT (NVARCHAR (+), Colxml.query (' Data (/record/exception/@type) [2] ')) as [type2]  , CONVERT (NVARCHAR), Colxml.query (' Data (/record/exception[2 ]/@type) [1] ') as [Type2],convert (NVARCHAR), Colxml.query (' Data (/record/exception/@type) ') as [type1 Type2]from #XMLTab

--can be used as query criteria: SELECT * from #XMLTab WHERE CONVERT (NVARCHAR, Colxml.query (' Data (/record/@dt) ')) = ' 2015-05-05 '

--use "value" in "Nodes" to query property values and element text values Select Node.c1.query (' Data (/record/@dt) '), CONVERT (NVARCHAR (+), Colxml.query (' Data (/record/@dt)) as [id], Node.c1.value (' (@dt) ', ' VARCHAR ') as [id], Node.c1.value (' (@dt) [1] ', ' VARCHAR (50) ') As [id], Node.c1.value (' (exception/@type) [1] ', ' VARCHAR (+) ') as [type1]  , Node.c1.value (' (Exception/error) [1] ', ' VARCHAR ') as [Error1] from  #XMLTabCROSS APPLY colxml.nodes ('/record ') as node (C1)

All right! ~21 diagram, test to this.

DML operations of the XML type do not begin to remember well, and it appears that only multiple write statements can be remembered. This is only a small part of the XML-related operation. XML also involves typed and untyped XML, XML type import and export, XML index (primary index/Secondary index/full-text index), use for XML, XML schema collection, and so on. Too much! ~ Waiting to learn!

SQL Server XML data type DML-related operations (graphic combination)