笔记73 XML在SQL中的使用

笔记73 XML在SQL中的使用

--XML在SQL中的使用

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL
    DROP TABLE #tb
GO

CREATE TABLE #tb
    (
      [id] INT IDENTITY  PRIMARY KEY ,
      [name] VARCHAR(4) ,
      [type] VARCHAR(10)
    )

INSERT  #tb
SELECT  '彪'   , '流氓'   UNION ALL
SELECT  '阿紫' , '流氓'   UNION ALL
SELECT  '小强' , '流氓'   UNION ALL
SELECT  '光辉' , '臭流氓' UNION ALL
SELECT  '小D'  , '臭流氓' UNION ALL
SELECT  '野子' , '臭流氓'

SELECT  *  FROM    [#tb]
--SELECT td=name ,'' FROM [#tb]



--------------开始查询-------------------------------------------------------------------------
--没有名称的列
--生成此 XML。 默认情况下，针对行集中的每一行，生成的 XML 中将生成一个相应的 <row> 元素。 这与 RAW 模式相同

SELECT  1 FOR     XML PATH

--延伸
SELECT  [name] + '' FROM    #tb FOR     XML PATH

--去掉<row> 元素
SELECT  [name] + '' FROM    #tb FOR     XML PATH('')

--具有名称的列
SELECT  [name] FROM    #tb FOR     XML PATH

SELECT  * FROM    #tb FOR     XML PATH

--列名以 @ 符号开头。'@id' 可以写其他的都可以例如：'@rowid'  '@guid' 反正加上AS '@id' 就把列移入属性
SELECT  id AS '@id' , [name] FROM    #tb FOR     XML PATH

--列名不以 @ 符号开头 把<row><name>彪</name></row>替换为<一群流氓><臭流氓>彪</臭流氓></一群流氓>
SELECT  [name] AS '臭流氓' FROM    #tb  FOR     XML PATH('一群流氓')

--列名以 @ 符号开头并包含斜杠标记 (/)
SELECT  id AS '@id' ,  [name] AS '一群流氓/臭流氓'  FROM    #tb  FOR     XML PATH
 

SELECT  [name] + '' FROM    #tb FOR     XML PATH('')
SELECT  [name] AS '臭流氓' FROM    #tb  FOR     XML PATH('一群流氓')
SELECT  id AS '@id' , [name] FROM    #tb FOR     XML PATH

-----------------------------------------------------------------------------------------------------
--名称指定为通配符的列

--如果指定的列名是一个通配符 (*)，则插入此列的内容时就像没有指定列名那样插入。

--如果此列不是 xml 类型的列，则此列的内容将作为文本节点插入
--这两句的效果是一样的
SELECT id  AS '@id',[name] AS '*'  FROM #tb FOR XML PATH
SELECT  id AS '@id',[name] + '' FROM    #tb FOR  XML PATH
--SELECT id  AS '@id',[name]  FROM #tb FOR XML PATH

--列名为 XPath 节点测试的列
--text()
--对于名为 text() 的列，该列中的字符串值将被添加为文本节点。

--comment()
--对于名为 comment() 的列，该列中的字符串值将被添加为 XML 注释。

--node()
--对于名为 node() 的列，结果与列名为通配符 (*) 时相同。

--处理指令（名称）
--如果列名为处理指令，该列中的字符串值将被添加为此处理指令目标名称的 PI 值

SELECT
id  AS '@id',
'臭流氓' AS 'text()',  --跟[name] AS '*'  一样效果
'一个臭流氓' as "processing-instruction(PI)",
'XML注释' AS 'comment()',
[name] AS 'node()'
FROM #tb FOR XML PATH
-- <row id="6">臭流氓<?PI 一个臭流氓?><!--XML注释-->野子</row>
---------------------------------------------------------------------
SELECT
id  AS '@id',
'臭流氓' AS 'text()',  --跟[name] AS '*'  一样效果
'XML注释' AS 'comment()',
[name] AS 'node()'  --对于名为 node() 的列，结果与列名为通配符 (*) 时相同。
FROM #tb FOR XML PATH ,TYPE

--<row id="1">
--  臭流氓<!--XML注释-->彪
--</row>
------------------------------------------------------------------------------------------------------

--带有指定为 data() 的路径的列名
--如果被指定为列名的路径为 data()，则在生成的 XML 中，该值将被作为一个原子值来处理。
--如果序列化中的下一项也是一个原子值，则将向 XML 中添加一个空格字符。
--这在创建列表类型化元素值和属性值时很有用。
-- 以下查询将检索产品型号 ID、名称和该产品型号中的产品列表。

SELECT id  AS '@id',
[name] AS '@name',
[type] AS 'data()' --结果与列名为通配符 (*) 时相同。
FROM #tb
WHERE id=1
FOR XML PATH



-----------------------------------------------------------------------------------------------
--默认情况下，列中的 Null 值映射为“缺少相应的属性、节点或元素”。
--通过使用 ELEMENTS 指令请求以元素为中心的 XML 并指定 XSINIL 来请求为 NULL 值添加元素，
--可以覆盖此默认行为，如以下查询所示：
--未指定 XSINIL，将缺少 <null> 元素。
INSERT  #tb
SELECT  '彪'   , '流氓'   UNION ALL
SELECT  '阿紫' ,  NULL    UNION ALL
SELECT  '小强' ,  '流氓'  UNION ALL
SELECT  '光辉' , '臭流氓' UNION ALL
SELECT   null  , '臭流氓' UNION ALL
SELECT  '野子' , '臭流氓'

--1  如果是null的话直接不显示那个元素<row id="2"><name>阿紫</name></row>
SELECT id  AS '@id',
[name] AS 'name',
[type] AS 'type'
FROM #tb
FOR XML PATH

--2 如果是null的话直接不显示那个元素  <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="2"><name>阿紫</name><type xsi:nil="true" /></row>
SELECT id  AS '@id',
[name] AS 'name',
[type] AS 'type'
FROM #tb
FOR XML PATH, ELEMENTS XSINIL


----------------------------------------------------------------------------------------------------------------------
--ROOT/TYPE/BINARY选项
SELECT id  AS '@id',
[name] ,
[type],
0x78786F6F AS 'VARBINARY'
FROM #tb
FOR XML PATH,ROOT('ForumConfig'),TYPE


ROOT('root'),--指定向产生的 XML 中添加单个顶级元素。 可以选择指定要生成的根元素名称。 默认值为“root”。

TYPE,--指定查询以 xml 类型返回结果。

BINARY BASE64--如果指定 BINARY Base64 选项，则查询所返回的任何二进制数据都用 base64 编码格式表示。
--若要使用 RAW 和 EXPLICIT 模式检索二进制数据，必须指定此选项。
--在 AUTO 模式中，默认情况下将二进制数据作为引用返回。