Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to get started with SQL Server XML query, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Introduction to sql xml:-- by jinjazz 1, xml: can recognize elements, attributes and values 2, xpath: addressing language, syntax format similar to windows directory lookup (face the wall if you haven't used the dir command) syntax format, these grammars can be combined into conditions: "." Express yourself, "..." For father, "/" means son, "/ /" means descendant, "name" means to look up by name, "@ name" means to find "set [condition]" by attribute, to take a subset of the set according to the condition. The condition can be a numerical value: numeric, last () Last ()-numerical equiBoolean values: all book nodes of position () 35 select @ data.query ('/ / book [price > 35]')-9, all book nodes of category= "WEB" select @ data.query ('/ / book [@ category= "WEB"]')-10, all book nodes select @ data.query of lang= "en" of title ('/ book/title [@ lang= "en"]')-- 11. Get title's lang= "en" and all book nodes with price > 35 select @ data.query ('/ / book [. / title [@ lang= "en"] or price > 35]')-- 12, get the first book of title's lang= "en" and price > 35 title select @ data.query ('/ book [. / title [@ lang= "en"] and price > 35]') .value ('(book/title) [1]') 'varchar (max)')-- 13, equivalent to 12 select @ data.value ('(/ / book [. / title [@ lang= "en"] and price > 35] / title) [1]', 'varchar (max)')-14, get the lang attribute select @ data.value of the first book of title and price > 35 ((/ / book [@ category= "WEB" and price > 35] / title) [1] / @ lang) [1]' 'varchar (max)')-15. Get the title select Tab.Col.value of the first book ('(book/title) [1]', 'varchar (max)') as title from @ data.nodes ('bookstore') as Tab (Col)-16, get the first author select Tab.Col.value of each book (' author [1]') 'varchar (max)') as title from @ data.nodes ('/ / book') as Tab (Col)-17. Get all the information about all book select T.C.value ('title [1]', 'varchar (max)') as title, T.C.value ('year [1]', 'int') as year, T.C.value (' title [1]', 'varchar (max)') as title, T.C.value ('price [1]', 'float') as price T.C.value ('author [1]', 'varchar (max)') as author1, T.C.value ('author [2]', 'varchar (max)') as author2, T.C.value ('author [3]', 'varchar (max)') as author3, T.C.value ('author [4]' 'varchar (max)') as author4 from @ data.nodes ('/ / book') as T (C)-18, get all the information about books that are not in Japanese (select T.C.value = "jp") and the price is greater than 35 select T.C.value ('title [1]', 'varchar (max)') as title, T.C.value ('year [1]', 'int') as year, T.C.value (' title [1]', 'varchar (max)') as title T.C.value ('price [1]', 'float') as price, T.C.value (' author [1]', 'varchar (max)') as author1, T.C.value ('author [2]', 'varchar (max)') as author2, T.C.value ('author [3]', 'varchar (max)') as author3, T.C.value ('author [4]') 'varchar (max)') as author4 from @ data.nodes ('/ / book [. / title [@ Langfang = "jp"] and price > 35]') as T (C)

The above is how to quickly start SQL Server XML query, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.