笔记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 模式中，默认情况下将二进制数据作为引用返回。
