笔记73 XML在SQL中的使用

笔记73 XML在SQL中的使用

  1 --XML在SQL中的使用
  2 
  3 IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL
  4     DROP TABLE #tb
  5 GO
  6 
  7 CREATE TABLE #tb
  8     (
  9       [id] INT IDENTITY  PRIMARY KEY ,
 10       [name] VARCHAR(4) ,
 11       [type] VARCHAR(10)
 12     )
 13 
 14 INSERT  #tb
 15 SELECT  ''   , '流氓'   UNION ALL
 16 SELECT  '阿紫' , '流氓'   UNION ALL
 17 SELECT  '小强' , '流氓'   UNION ALL
 18 SELECT  '光辉' , '臭流氓' UNION ALL
 19 SELECT  '小D'  , '臭流氓' UNION ALL
 20 SELECT  '野子' , '臭流氓'
 21 
 22 SELECT  *  FROM    [#tb]
 23 --SELECT td=name ,'' FROM [#tb]
 24 
 25 
 26 
 27 --------------开始查询-------------------------------------------------------------------------
 28 --没有名称的列
 29 --生成此 XML。 默认情况下,针对行集中的每一行,生成的 XML 中将生成一个相应的 <row> 元素。 这与 RAW 模式相同
 30 
 31 SELECT  1 FOR     XML PATH
 32 
 33 --延伸
 34 SELECT  [name] + '' FROM    #tb FOR     XML PATH
 35 
 36 --去掉<row> 元素
 37 SELECT  [name] + '' FROM    #tb FOR     XML PATH('')
 38 
 39 --具有名称的列
 40 SELECT  [name] FROM    #tb FOR     XML PATH
 41 
 42 SELECT  * FROM    #tb FOR     XML PATH
 43 
 44 --列名以 @ 符号开头。'@id' 可以写其他的都可以例如:'@rowid'  '@guid' 反正加上AS '@id' 就把列移入属性
 45 SELECT  id AS '@id' , [name] FROM    #tb FOR     XML PATH
 46 
 47 --列名不以 @ 符号开头 把<row><name>彪</name></row>替换为<一群流氓><臭流氓>彪</臭流氓></一群流氓>
 48 SELECT  [name] AS '臭流氓' FROM    #tb  FOR     XML PATH('一群流氓')
 49 
 50 --列名以 @ 符号开头并包含斜杠标记 (/)
 51 SELECT  id AS '@id' ,  [name] AS '一群流氓/臭流氓'  FROM    #tb  FOR     XML PATH
 52  
 53 
 54 SELECT  [name] + '' FROM    #tb FOR     XML PATH('')
 55 SELECT  [name] AS '臭流氓' FROM    #tb  FOR     XML PATH('一群流氓')
 56 SELECT  id AS '@id' , [name] FROM    #tb FOR     XML PATH
 57 
 58 -----------------------------------------------------------------------------------------------------
 59 --名称指定为通配符的列
 60 
 61 --如果指定的列名是一个通配符 (*),则插入此列的内容时就像没有指定列名那样插入。
 62 
 63 --如果此列不是 xml 类型的列,则此列的内容将作为文本节点插入
 64 --这两句的效果是一样的
 65 SELECT id  AS '@id',[name] AS '*'  FROM #tb FOR XML PATH
 66 SELECT  id AS '@id',[name] + '' FROM    #tb FOR  XML PATH
 67 --SELECT id  AS '@id',[name]  FROM #tb FOR XML PATH
 68 
 69 --列名为 XPath 节点测试的列
 70 --text()
 71 --对于名为 text() 的列,该列中的字符串值将被添加为文本节点。
 72 
 73 --comment()
 74 --对于名为 comment() 的列,该列中的字符串值将被添加为 XML 注释。
 75 
 76 --node()
 77 --对于名为 node() 的列,结果与列名为通配符 (*) 时相同。
 78 
 79 --处理指令(名称)
 80 --如果列名为处理指令,该列中的字符串值将被添加为此处理指令目标名称的 PI 值
 81 
 82 SELECT
 83 id  AS '@id',
 84 '臭流氓' AS 'text()',  --跟[name] AS '*'  一样效果
 85 '一个臭流氓' as "processing-instruction(PI)",
 86 'XML注释' AS 'comment()',
 87 [name] AS 'node()'
 88 FROM #tb FOR XML PATH
 89 -- <row id="6">臭流氓<?PI 一个臭流氓?><!--XML注释-->野子</row>
 90 ---------------------------------------------------------------------
 91 SELECT
 92 id  AS '@id',
 93 '臭流氓' AS 'text()',  --跟[name] AS '*'  一样效果
 94 'XML注释' AS 'comment()',
 95 [name] AS 'node()'  --对于名为 node() 的列,结果与列名为通配符 (*) 时相同。
 96 FROM #tb FOR XML PATH ,TYPE
 97 
 98 --<row id="1">
 99 --  臭流氓<!--XML注释-->彪
100 --</row>
101 ------------------------------------------------------------------------------------------------------
102 
103 --带有指定为 data() 的路径的列名
104 --如果被指定为列名的路径为 data(),则在生成的 XML 中,该值将被作为一个原子值来处理。
105 --如果序列化中的下一项也是一个原子值,则将向 XML 中添加一个空格字符。
106 --这在创建列表类型化元素值和属性值时很有用。
107 -- 以下查询将检索产品型号 ID、名称和该产品型号中的产品列表。
108 
109 SELECT id  AS '@id',
110 [name] AS '@name',
111 [type] AS 'data()' --结果与列名为通配符 (*) 时相同。
112 FROM #tb
113 WHERE id=1
114 FOR XML PATH
115 
116 
117 
118 -----------------------------------------------------------------------------------------------
119 --默认情况下,列中的 Null 值映射为“缺少相应的属性、节点或元素”。
120 --通过使用 ELEMENTS 指令请求以元素为中心的 XML 并指定 XSINIL 来请求为 NULL 值添加元素,
121 --可以覆盖此默认行为,如以下查询所示:
122 --未指定 XSINIL,将缺少 <null> 元素。
123 INSERT  #tb
124 SELECT  ''   , '流氓'   UNION ALL
125 SELECT  '阿紫' ,  NULL    UNION ALL
126 SELECT  '小强' ,  '流氓'  UNION ALL
127 SELECT  '光辉' , '臭流氓' UNION ALL
128 SELECT   null  , '臭流氓' UNION ALL
129 SELECT  '野子' , '臭流氓'
130 
131 --1  如果是null的话直接不显示那个元素<row id="2"><name>阿紫</name></row>
132 SELECT id  AS '@id',
133 [name] AS 'name',
134 [type] AS 'type'
135 FROM #tb
136 FOR XML PATH
137 
138 --2 如果是null的话直接不显示那个元素  <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="2"><name>阿紫</name><type xsi:nil="true" /></row>
139 SELECT id  AS '@id',
140 [name] AS 'name',
141 [type] AS 'type'
142 FROM #tb
143 FOR XML PATH, ELEMENTS XSINIL
144 
145 
146 ----------------------------------------------------------------------------------------------------------------------
147 --ROOT/TYPE/BINARY选项
148 SELECT id  AS '@id',
149 [name] ,
150 [type],
151 0x78786F6F AS 'VARBINARY'
152 FROM #tb
153 FOR XML PATH,ROOT('ForumConfig'),TYPE
154 
155 
156 ROOT('root'),--指定向产生的 XML 中添加单个顶级元素。 可以选择指定要生成的根元素名称。 默认值为“root”。
157 
158 TYPE,--指定查询以 xml 类型返回结果。
159 
160 BINARY BASE64--如果指定 BINARY Base64 选项,则查询所返回的任何二进制数据都用 base64 编码格式表示。
161 --若要使用 RAW 和 EXPLICIT 模式检索二进制数据,必须指定此选项。
162 --在 AUTO 模式中,默认情况下将二进制数据作为引用返回。

 

posted @ 2013-08-02 22:56 桦仔 阅读(...) 评论(...) 编辑 收藏