The most basic rule is that xml data cannot be compared with other data types, that is, xml data cannot appear on any side of the equal sign. For our database, we didn't want everyone to use the XML data type. Since this is very useful, and the platform and various product lines are using this data type
The most basic rule is that xml data cannot be compared with other data types, that is, xml data cannot appear on any side of the equal sign. For our database, we didn't want everyone to use the XML data type. Since this is very useful, and the platform and various product lines are using this data type
Basic rules:
Data of the xml type and between the xml type and other data types cannot be compared, that is, data of the xml type cannot appear on any side of the equal sign.
For our database, we didn't want everyone to use the XML data type. Since this is very useful and the platform and product lines are using this data type, the following requirements are imposed on XML:
1. Batch update of XML data is not allowed. When updating, you must add restrictions in the Where clause, such as userid.
2. batch query of XML types is not allowed. You also need to add a condition, such as userid, to the Where clause. Add the xml data conditions.
Query Class
1. query () method: returns all xml rows that meet the conditions. It can only be used in the select clause.
2. value () method: returns the scalar value extracted from the xml node. The data type of the returned scalar must be specified in the second parameter of the value () method, so the value () method can be compared with other scalar. It can be used in select clause and where clause.
3. exist () method: returns 0 or 1 of the int type scalar. Check the existence of xml data types in each row. It can be used in select clause and where clause.
4. nodes () method: returns a table with only one column and the column of the table is of the xml data type. Therefore, the nodes () method can only appear in the from clause.
Modification class:
Modify ()
Modify (insert .....)
Modify (delete .....)
Modify (replace ....)
Use the AssessmentUserTestResult table in the company's beisenuser database for testing.
-- Query Functions
Select ID, fk_beisenuser_id, Result
-- Select *
From AssessmentUserTestResult
Where ID = 1
-- Query
-- Query () method
-- Query all xml data of a part Node
Select id, fk_beisenuser_id, result. query ('/TestResult/test/partlist/part ')
From AssessmentUserTestResult
Where ID = 2
Select id, fk_beisenuser_id, result. query ('/TestResult/test/partlist/part/starttime ')
From AssessmentUserTestResult
Where ID = 2
Select id, fk_beisenuser_id, result. query ('/TestResult/test/partlist/part/questionlist/question ')
From AssessmentUserTestResult
Where ID = 2
Select id, fk_beisenuser_id, result. query ('/TestResult/test/partlist/part/questionlist/question [1]')
From AssessmentUserTestResult
Where ID = 2
Select id, fk_beisenuser_id, result. query ('/TestResult/test/partlist/part/questionlist/question [2]')
From AssessmentUserTestResult
Where ID = 2
-- Value () method
Select id, fk_beisenuser_id, result. value ('(/TestResult/test/partlist/part/starttime) [1]', 'datetime') as starttime
From AssessmentUserTestResult
Where ID = 2
Select Result. value ('(/TestResult/test/partlist/part/questionlist/question [1]/id) [1]', 'varchar (500 )')
From AssessmentUserTestResult
Where ID = 2
Select *
From AssessmentUserTestResult
Where Result. value ('(/TestResult/test/partlist/part/questionlist/question/id) [1]', 'uniqueidentifier') = '2142ff59-BBCA-4D30-8325-F188564EE109'
And ID = 2
Select *
From AssessmentUserTestResult
Where Result. value ('(/TestResult/test/partlist/part/questionlist/question [2]/id) [1]', 'uniqueidentifier') = '68a0d7b3-CF21-4BB4-91C0-F93D0AB35F09'
And ID = 2
-- Exist () method
Select *
From AssessmentUserTestResult
Where Result. exist ('(/TestResult/test/partlist/part/questionlist/question [1]/result)') = 1
And id = 2
-- Node () method
-- He boss may be interested in the following results.
Select B. loc. query ('.')
From AssessmentUserTestResult
Cross apply result. nodes ('/TestResult/test/partlist/part/questionlist/question') as B (loc)
Where a. ID = 2
-- Modify data
-- Insert xml data into the part Node
-- Modify (insert) parameter as first, as last, before, after
Update AssessmentUserTestResult
Set Result. modify ('
Insert
Aaaaaa-27E7-4240-8455-bbbbbbbbbbbbb
1
Into (/TestResult/test/partlist/part) [1]'
)
Where ID = 1
-------------------
Update AssessmentUserTestResult
Set Result. modify ('
Insert
Aaaaaa-27E7-4240-8455-bbbbbbbbbbbbb
1
As last into (/TestResult/test/partlist/part) [1]'
)
Where ID = 1
-- Modify (delete)
--
-- Delete content
Update AssessmentUserTestResult
Set Result. modify ('
Delete (/TestResult/test/partlist/part/questionlist/question) [1]/id/text ()'
)
Where ID = 1
-- Delete a node
Update AssessmentUserTestResult
Set Result. modify ('
Delete (/TestResult/test/partlist/part/questionlist/question [1]/id) [1]'
)
Where ID = 1
-- Delete attributes
Update AssessmentUserTestResult
Set Result. modify ('
Delete (/TestResult/test/partlist/part/questionlist/question [1]/@ category) [1]'
)
Where ID = 1
Select result. query ('(/TestResult/test/partlist/part/questionlist/question) [1]')
From AssessmentUserTestResult
Where ID = 1
-- Modify (replace)
-- Replace content
Select result. query ('(/TestResult/test/partlist/part/questionlist/question [2])')
From AssessmentUserTestResult
Where ID = 1
Update AssessmentUserTestResult
Set Result. modify ('
Replace value of (/TestResult/test/partlist/part/questionlist/question [2]/result [1]/text () [1]
With ("4 ")'
)
Where ID = 1
/*
-- Replace attributes
Update AssessmentUserTestResult
Set Result. modify ('
Replace value of (/TestResult/test/partlist/part/questionlist/question [2]/@ result) [1]
With ("aaaaa ")'
)
Where ID = 1
*/
---------------------------------------------------------------------------- Shamelessly split -------------------------------------------------------------
Declare @ personid int, @ wdid uniqueidentifier, @ value int
Declare my_cursor cursor
Select perid, wdid, value from temp_duoyu
Open my_cursor
Fetch next from my_cursor into @ personid, @ wdid, @ value
While @ FETCH_STATUS = 0
Begin
Update
Set testcent. modify ('
Replace value of (/TestCent/test [4]/level1originalcent/centlist [id = SQL: variable ("@ wdid")]/value/text () [1]
With SQL: variable ("@ value ")
')
From AssessmentUserTestResult
Where a. ID = @ personid
Fetch next from my_cursor into @ personid, @ wdid, @ value
End
Close my_cursor
Deallocate my_cursor
---------------------------------------------------------------------------- Shamelessly split -------------------------------------------------------------
