SQL Server for XML application

I accidentally read an application that uses the for XML Path to retrieve multiple rows of Data separated by commas (,). I used for XML auto before. To find out the differences, I went to msdn for a tutorial.

Mark the learning result here. To learn more, log on to msdn

In addition to the above two modifiers, there are also two modifiers for XML. There are four types:

1、RAW

2、AUTO

3、PATH

4、EXPLICIT

Basically, the functions are arranged in a forward order based on the strengths and weaknesses of the functions. Let's talk less and look at the Code directly:

For XML Learning
-- Raw-select corpcode from DBO. kvp_corpinfo for XML auto ('') -- the row name can be marked only in raw or path mode of XML. Select corpcode from DBO. kvp_corpinfo for XML raw ('') -- the row tag is omitted (the empty row tag name) and cannot be used together with the attribute-centric for XML serialization .? Select corpcode from DBO. kvp_corpinfo for XML raw (''), elements select Top 100 productid, productnamefrom DBO. bas_productinfofor XML raw, elements; -- for XML raw, type; -- the results are named differently. msdn: You can selectively specify the type command to retrieve the result as an XML type. The type command does not change the result content. Only the Data Type of the result is affected. -- Autoselect top 1 productid, productnamefrom DBO. bas_productinfo afor XML auto, elements; -- select orderinfo. salesorderid, orderinfo. totalamount, orderlist. salesorderid, orderlist. salesorderlistid, orderlist. productid, orderlist. selfpricefrom DBO. ope_salesorderrecord orderinfo, DBO. ope_salesorderlist orderlistwhere orderinfo. salesorderid = orderlist. salesorderidfor XML auto, elementsselect orderlis T. salesorderid, orderlist. salesorderlistid, orderlist. productid, orderlist. selfprice, orderinfo. salesorderid, orderinfo. totalamountfrom DBO. ope_salesorderrecord orderinfo, DBO. ope_salesorderlist orderlistwhere orderinfo. salesorderid = orderlist. salesorderidfor XML Auto/* when a parent element is created, each parent node to be created is compared. If the value is different, a new parent element is added to XML, if the values are the same, the property is compared, and so on. When comparing these column values, if any column to be compared is of the text, ntext, image, or XML type, for XML considers them different even if their values may be the same, and do not compare them. This is because comparison of large objects is not supported. These elements are added to the results of each selected row. Note that (n) varchar (max) and varbinary (max) columns are compared. */-- Explicit/* [XML for complex structures] common format: elementname! Tagnumber! Attributename! Directivedireve: If directive and attributename (such as customer! 1), it implies an element command (such as customer! 1 !! Element), and the column data is included in elementname. One purpose is to encode the value as ID, idref, and idrefs. You can specify the ID, idref, and idrefs keywords as direves ves. These commands overwrite the property type. This allows you to create links in the document. In addition, except for entity encoding, the hide, element, elementxsinil, XML, xmltext, and CDATA commands are the same as the element commands. Http://msdn.microsoft.com/zh-cn/library/ms189068824/select 1 as tag, null as parent, A. corpcode as [personnel! 1! Name], null as [personnel information! 2! Age! XML] From DBO. kvp_corpinfo A where corpcode <> ''unionselect 2 as tag, 1 as parent,. corpcode, B. corpid from DBO. kvp_corpinfo A, DBO. kvp_corpinfo B where. corpcode <> ''and. corpcode = B. corpcodeorder by [personnel! 1! Name], tag -- sort here for nested node XML EXPLICIT--PATHSELECT 2 + 2 for XML Path -- value, XML: <row> 4 </row> select 2 + 2' @ t' for XML Path -- attribute, XML: <row T = "4"/> select 2 + 2 t for XML Path -- node, XML: <row> <t> 4 </T> </row> select 2 + 2't/F' for XML Path -- Multi-level node, XML: <row> <t> <F> 4 </F> </T> </row> -- specify the column name as a wildcard/* If the specified column name is a wildcard (* ), the content of this column is inserted as if the column name is not specified. If this column is not an XML column, the content of this column will be inserted as a text node */select corpcode '@ Code', corpcode '*',':''*', corpname '*' from DBO. kvp_corpinfo where corpcode <> ''for XML Path/* <row code =" 0021 "> 0021: beijing ** Co., Ltd. </row> <row code = "0006"> 0006: Nanjing ** Co., Ltd. </row> <row code = "0024"> 0024: shanghai ** Co., Ltd. </row> <row code = "009a"> 009a: dcgou </row> <row code = "0046"> 0046: beijing ** Supply Chain Service Co., Ltd. </row> <row code = "00x8"> 00x8: Guangdong ** Co., Ltd. </row> */select ruleid, rulename, Param. query ('/rule')' * 'from DBO. sys_ruleinfo where ruleid = 10115000000011for XML Path -- column names are text ()/comment ()/node () select 2 + 2' text () columns tested by the XPath Node () 'For XML Path -- = writetext (); select 2 + 2' comment () 'for XML Path -- = writecomment (); select 2 + 2' node () 'for XML Path -- = * -- data () Select corpcode 'data ()' from DBO. kvp_corpinfo where corpcode <> ''for XML Path ('') -- XML: 0021 0006 0024 009a 0046 00x8select * From sys_ruleinfo -- use path, obtain the select corpcode + ',' from DBO. kvp_corpinfo where corpcode <> ''for XML Path ('') -- XML: 0024,009, A, x 8, select ''' + corpcode + ''', 'from DBO. kvp_corpinfo where corpcode <> ''for XML Path ('') -- XML: '000000', '000000', '0000009a ', '000000 ', '00x8 ',