Usage of XML in SQL SERVER

Usage of XML in SQL SERVER

USE daniel_test_db;
GO


/******************************************************************
*DESCRIPTION : 创建xml表
*CREATE DATE : 2020-07-17
*AUTHOR : Yuan Lau
********************************************************************/


--创建表
CREATE TABLE T_Xml
(
    Id INT IDENTITY PRIMARY KEY NOT NULL,
    XmlData XML
);


--插入基本数据
INSERT INTO dbo.T_Xml
(
    XmlData
)
VALUES
('<book id="0001">
<title>SqlServer2005</title>
<author>Fly</author>
<price>21</price>
</book>
'   ),
('<book id="0002">
<title>Java基础</title>
<author>Daniel</author>
<price>31</price>
</book>
'),
('<book id="0003">
<title>Redis知识手册</title>
<author>Tom</author>
<price>40</price>
</book>
'),
('<book id="0004">
<title>深度理解C#</title>
<author>Jerry</author>
<price>52</price>
</book>
'),
('<book id="0005">
<title>零基础学Python</title>
<author>Daniel</author>
<price>88</price>
</book>
');


--查询各个节点
SELECT Title = XmlData.query('/book/title'),
       Author = XmlData.query('/book/author')
FROM dbo.T_Xml;


--查询书籍信息
SELECT Title = XmlData.value('(/book/title)[1]', 'nvarchar(max)'),
       Author = XmlData.value('(/book/author)[1]', 'nvarchar(max)')
FROM dbo.T_Xml;


--查询数目编号为0001的书的信息
SELECT Title = XmlData.value('(/book/title)[1]', 'nvarchar(max)'),
       BookId = XmlData.value('(/book/@id)[1]', 'nvarchar(max)')
FROM dbo.T_Xml
WHERE XmlData.value('(/book/@id)[1]', 'nvarchar(max)') = '0001';


--修改0001这本书的价格
UPDATE dbo.T_Xml
SET XmlData.modify('replace value of (/book[@id="0001"]/price/text())[1] with "13"');
SELECT *
FROM dbo.T_Xml;


--修改所有作者为Yuan Lau
UPDATE dbo.T_Xml
SET XmlData.modify('replace value of (/book/author/text())[1] with "Yuan Lau"');
SELECT *
FROM dbo.T_Xml;


--查询编号为0001的信息
SELECT Title = XmlData.value('(/book/title)[1]', 'nvarchar(max)'),
       BookId = XmlData.value('(/book/@id)[1]', 'nvarchar(max)'),
       Author = XmlData.value('(/book/author)[1]', 'nvarchar(max)'),
       Price = XmlData.value('(/book/price)[1]', 'decimal(7,2)')
FROM dbo.T_Xml
WHERE XmlData.value('(/book/@id)[1]', 'nvarchar(max)') = '0001';


--插入属性（所有数据）
UPDATE dbo.T_Xml
SET XmlData.modify('insert attribute isbn {"12300321"} into (/book)[1]');
SELECT *
FROM dbo.T_Xml;


--插入属性（满足条件的数据）
UPDATE dbo.T_Xml
SET XmlData.modify('insert attribute rebate {"0.85"} into (/book)[1]')
WHERE XmlData.value('(/book/@id)[1]', 'nvarchar(max)') = '0001';
SELECT *
FROM dbo.T_Xml;


--查看是否存在属性isbn
SELECT Isbn = XmlData.value('(/book/@isbn)[1]', 'nvarchar(max)'),
       BookId = XmlData.value('(/book/@id)[1]', 'nvarchar(max)')
FROM dbo.T_Xml
WHERE XmlData.value('(/book/@id)[1]', 'nvarchar(max)') = '0001';


--在编号为0001的添加子节点 category 为 Computer 的分类
UPDATE dbo.T_Xml
SET XmlData.modify('insert <category>Computer</category> before (/book[@id=0001]/author)[1]');
SELECT *
FROM dbo.T_Xml;


--查看是否添加了category
SELECT Category = XmlData.value('(/book/category)[1]', 'nvarchar(max)'),
       BookID = XmlData.value('(/book/@id)[1]', 'nvarchar(max)'),
       XmlData
FROM dbo.T_Xml
WHERE XmlData.value('(/book/@id)[1]', 'nvarchar(max)') = '0001';


--删除节点
UPDATE dbo.T_Xml
SET XmlData.modify('delete /book[@id=0001]/category');
SELECT *
FROM dbo.T_Xml;


--查看是否删除了category节点
SELECT Category = XmlData.value('(/book/category)[1]', 'nvarchar(max)'),
       BookID = XmlData.value('(/book/@id)[1]', 'nvarchar(max)'),
       XmlData
FROM dbo.T_Xml
WHERE XmlData.value('(/book/@id)[1]', 'nvarchar(max)') = '0001';


--nodes()  查询 book的编码
SELECT BookID = ids.value('@id', 'varchar(max)'),
       Title = ids.value('(title)[1]', 'nvarchar(max)')
FROM dbo.T_Xml
    CROSS APPLY XmlData.nodes('/book') AS X(ids);


--exist()
SELECT BookID = XmlData.value('(/book/@id)[1]', 'nvarchar(max)')
FROM dbo.T_Xml
WHERE XmlData.exist('(/book/@id)') = 1; --判断是否存在



/*************************************************************************************************************************************************************/



/******************************************************************
*DESCRIPTION : FOR XML PATH 操作练习
*CREATE DATE :  2020-07-17	
*AUTHOR : Yuan Lau
********************************************************************/

--创建表
CREATE TABLE Books
(
    ID NVARCHAR(32) NOT NULL,
    Name NVARCHAR(64)
);


--添加测试数据
INSERT INTO Books
VALUES
('0001', 'MSSQLServer2005'), --书名MSSQLServer2005
('0002', 'MSSQLServer2008'), --书名MSSQLServer2008
('0003', 'MSSQLServer2012'); --书名MSSQLServer2012


--用法1
SELECT ID,
       Name
FROM [dbo].[Books]
FOR XML AUTO;


--用法2
SELECT ID,
       Name
FROM [dbo].[Books]
FOR XML AUTO, ELEMENTS, ROOT('books');


--用法3
SELECT ID AS 'BookID',
       Name AS 'BookName'
FROM [dbo].[Books]
FOR XML RAW;


--用法4
SELECT ID,
       Name
FROM [dbo].[Books]
FOR XML RAW('book'), ELEMENTS, ROOT('books');


--用法5
SELECT ID,
       Name
FROM [dbo].[Books]
FOR XML PATH('');


--用法6
SELECT ID AS 'Detail/@ID',
       Name AS 'Detail/Name'
FROM [dbo].[Books]
FOR XML PATH('Book'), ROOT('Books');


--使用场景1
SELECT STUFF(
       (
           SELECT ';' + Name FROM [dbo].[Books] FOR XML PATH('')
       ),
       1,
       1,
       ''
            );

• 微软官方文档地址：XML