17、MSSQL使用XML
参考https://www.cnblogs.com/zhaoshujie/p/9594659.html https://docs.microsoft.com/zh-cn/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-ver15
入门
测试一：结果集转XML
CREATE TABLE #T(ID INT,NAME NVARCHAR(50));
INSERT INTO #T ( ID, NAME )
VALUES(1,'QING'),(2,'ming')
--结果集生成属性节点XML
--<row ID="1" NAME="QING"/><row ID="2" NAME="ming"/>
SELECT ID,NAME FROM #T FOR XML RAW
--<_x0023_T ID="1" NAME="QING"/><_x0023_T ID="2" NAME="ming"/>
SELECT ID,NAME FROM #T FOR XML AUTO
--生成元素结点xml
--<row><ID>1</ID><NAME>QING</NAME></row><row><ID>2</ID><NAME>ming</NAME></row>
SELECT ID,NAME FROM #T FOR XML PATH
--返回逗号间隔 QING,ming,
SELECT NAME+',' FROM #T FOR XML PATH('')
测试二：XML转结果集
--如果XML是属性xml,OPENXML里设置参数0
--XML生成数据集
DECLARE @idoc INT;
DECLARE @xmldoc NVARCHAR(400);
SET @xmldoc='<ROOT><row ID="1" NAME="QING"/><row ID="2" NAME="ming"/></ROOT>';
EXEC sys.sp_xml_preparedocument @idoc OUTPUT,@xmldoc;
SELECT ID,NAME FROM OPENXML(@idoc,'/ROOT/row',0)
WITH
(
ID INT,
NAME NVARCHAR(50)
)
EXEC sys.sp_xml_removedocument @idoc;
--如果XML是元素xml,OPENXML里设置参数2
--XML生成数据集
DECLARE @idoc INT;
DECLARE @xmldoc NVARCHAR(400);
SET @xmldoc='<ROOT><row><ID>1</ID><NAME>QING</NAME></row><row><ID>2</ID><NAME>ming</NAME></row></ROOT>';
EXEC sys.sp_xml_preparedocument @idoc OUTPUT,@xmldoc;
SELECT ID,NAME FROM OPENXML(@idoc,'/ROOT/row',2)
WITH
(
ID INT,
NAME NVARCHAR(50)
)
EXEC sys.sp_xml_removedocument @idoc;
都返回结果集
注意上面的两个xml是不一样的，一个数属性xml，一个是元素xml
使用Xquery
需要先了解xpath语法https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms189075(v=sql.110) XML数据类型方法：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms190798%28v%3dsql.110%29 XML数据类型方法：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms190798%28v%3dsql.110%29 在线博客https://www.cnblogs.com/benwu/articles/4834917.html T-SQL XQuery包含如下函数query(XPath条件)： 结果为 xml 类型; 返回由符合条件的节点组成的非类型化的 XML 实例value(XPath条件,数据类型)：结果为指定的标量值类型; xpath条件结果必须唯一exist(XPath条件)：结果为布尔值; 表示节点是否存在,如果执行查询的 XML 数据类型实例包含NULL则返回NULLnodes(XPath条件): 返回由符合条件的节点组成的一行一列的结果表
示例
declare @xmlDoc xml;
set @xmlDoc='<book id="0001">
<title>C Program</title>
<author>David</author>
<price>21</price>
</book>'
1、使用query查询
select @xmlDoc.query('/book/title')
2、使用value 查询
必须指定[1]否则会报错
select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')
如果xml中多个title
declare @xmlDoc xml;
set @xmlDoc='<book id="0001">
<title>C Program</title>
<title>JAVA</title>
<author>David</author>
<price>21</price>
</book>'
select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)') --返回第一个值
select @xmlDoc.value('(/book/title)[2]', 'nvarchar(max)') --返回第二个值
3、查询属性值
无论是使用query还是value，都可以很容易的得到一个节点的某个属性值，例如，我们很希望得到book节点的id，我们这里使用value方法进行查询，语句为：
declare @xmlDoc xml;
set @xmlDoc='<book id="0001">
<title>C Program</title>
<title>JAVA</title>
<author>David</author>
<price>21</price>
</book>'
select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')
运行结果如图：
4、使用xpath进行查询
xpath是.net平台下支持的，统一的Xml查询语句。使用XPath可以方便的得到想要的节点，而不用使用where语句。例如，我们在@xmlDoc中添加了另外一个节点，重新定义如下：
declare @xmlDoc xml;
set @xmlDoc='
<root>
<book id="0001">
<title>C# Program</title>
<author>Jerry</author>
<price>50</price>
</book>
<book id="0002">
<title>Java Program</title>
<author>Tom</author>
<price>49</price>
</book>
</root>'
--得到id为0002的book节点
select @xmlDoc.query('(/root/book[@id="0002"])')
上面的语句可以独立运行，它得到的是id为0002的节点。运行结果如下图：
5、修改节点操作modify()
我们希望将id为0001的书的价钱(price)修改为100， 我们就可以使用modify方法。代码如下：
declare @xmlDoc xml;
set @xmlDoc='
<root>
<book id="0001">
<title>C# Program</title>
<author>Jerry</author>
<price>50</price>
</book>
<book id="0002">
<title>Java Program</title>
<author>Tom</author>
<price>49</price>
</book>
</root>'
set @xmlDoc.modify('replace value of (/root/book[@id=0001]/price/text())[1] with "100"')
--得到id为0001的book节点
select @xmlDoc.query('(/root/book[@id="0001"])')
注意：modify方法必须出现在set的后面。运行结果如图：
6、删除节点
接下来我们来删除id为0002的节点，代码如下：--删除节点id为0002的book节点
set @xmlDoc.modify('delete /root/book[@id=0002]')
select @xmlDoc
运行结果如图：
7、添加节点
很多时候，我们还需要向xml里面添加节点，这个时候我们一样需要使用modify方法。下面我们就向id为0001的book节点中添加一个ISBN节点，代码如下：
--添加节点
SET @xmlDoc.modify('insert <isbn>789-123-456</isbn> before (/root/book[@id=0001]/price)[1]')
8、添加和删除属性
当你学会对节点的操作以后，你会发现，很多时候，我们需要对节点进行操作。这个时候我们依然使用modify方法，例如，向id为0001的book节点中添加一个date属性，用来存储出版时间。代码如下：
--添加属性
set @xmlDoc.modify('insert attribute date{"2008-11-27"} into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
如果你想同时向一个节点添加多个属性，你可以使用一个属性的集合来实现，属性的集合可以写成：(attribute date{"2008-11-27"}, attribute year{"2008"})，你还可以添加更多。这里就不再举例了。
9、删除属性
删除一个属性，例如删除id为0001 的book节点的id属性，我们可以使用如下代码：
--删除属性
set @xmlDoc.modify('delete root/book[@id="0001"]/@id')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图：
10、修改属性
修改属性值也是很常用的，例如把id为0001的book节点的id属性修改为0005，我们可以使用如下代码：
--修改属性
set @xmlDoc.modify('replace value of (root/book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/root/book)[1]')
经过上面的学习，相信你已经可以很好的在SQL中使用Xml类型了，下面是我们没有提到的，你可以去其它地方查阅：exist()方法，用来判断指定的节点是否存在，返回值为true或false； nodes()方法，用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行。
999、问题记录
https://docs.microsoft.com/zh-cn/sql/relational-databases/xml/retrieve-and-query-xml-data?view=sql-server-2017错误：
根据微软的文档：当使用 xml 数据类型方法查询 xml 类型列或变量时，以下选项必须按照所显示的内容进行设置。
EXEC s_g_SaveReceipt10FromMO @xml='<Body>
<Details iRowNo="1" cPosition="00000" cWhCode="新成品仓" cCode="WO180806002R" cDepCode="采血管车间" cMaker="陈家鸣" cInvCode="101810013" iQuantity="1800" MoDId="200124145" cBarCode="11034,101810013,180806,1800,2283-257M,00010" SortSeq="1" cRdCode="产成品入库" cBatchProperty7="" cBatch="180806" BoxSize="" BoxNum="1" />
</Body>'
--下面是是获取上面xml中其中一个属性的值，报了上面截图的错误！
DECLARE @xmlBarCode NVARCHAR(MAX),@sCode NVARCHAR(max);
SET @xmlBarCode=CAST(@xml.value('(/Body/Details/@cBarCode)[1]','NVARCHAR(max)') AS NVARCHAR(max));
SET @sCode=(SELECT cDesc FROM dbo.Logs WHERE cXml.value('(/Body/Details/@cBarCode)[1]','NVARCHAR(max)')=@xmlBarCode AND cFlag='OK');
IF @sCode IS NOT NULL
BEGIN
IF EXISTS(SELECT * FROM dbo.RdRecord10 WHERE cCode=@sCode)
BEGIN
SET @ErrorMsg='保存失败:检测到当前操作重复扫描!';
RAISERROR(@ErrorMsg,16,1);
END
END
解决办法：在存储过程前面设置
SET 选项 所需值
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
----------------------------------------
如果这些选项未按照此处所示进行设置，则对 xml 数据类型方法的查询和修改将失败。
应用xml.nodes()
DECLARE @DOC XML ='
<books>
<book category="C#">
<title language="en">C# in Depth</title>
<author>John Skeet</author>
<year>2010</year>
<price>62.30</price>
</book>
<book category="C#">
<title language="cn">Effective C#</title>
<author>Bill Wagner</author>
<year>2010</year>
<price>49.00</price>
</book>
<book category="MSSQL">
<title language="cn">SQL2008 技术内幕</title>
<author>Itzik Ben-Gan</author>
<year>2010</year>
<price>90.20</price>
</book>
<book category="javascipt">
<title language="cn">JavaScript权威指南</title>
<author>David Flanagan</author>
<year>2007</year>
<price>87.20</price>
</book>
</books>
';
--查询所有书籍的分类
SELECT
T.C.value('@category','VARCHAR(16)')
FROM @DOC.nodes('/books/book') AS T (C);
--查询所有C#书籍的名称，作者，价格，年份
WITH B AS
(
SELECT @DOC.query('//book[@category="C#"]') AS BookNode
)
SELECT
T.C.value('title[1]/@language','VARCHAR(32)') AS [language],
T.C.value('title[1]','VARCHAR(32)') AS title,
T.C.value('author[1]','VARCHAR(16)') AS author,
T.C.value('year[1]','INT') AS [year],
T.C.value('price[1]','DECIMAL(19,2)') AS price
FROM B
CROSS APPLY B.BookNode.nodes('/book') AS T (C);
--查询所有书籍的语言和名称
SELECT
T.C.value('@language[1]','varchar(56)') AS [Language],
T.C.value('.','VARCHAR(56)') AS TITLE
FROM @DOC.nodes('/books/book/title') AS T (C);
结合C#使用
/// <summary>
/// 生成XML
/// </summary>
/// <param name="listBarCodes"></param>
/// <returns></returns>
public string AllBarCodeXML(List<ALLBarCode> listBarCodes)
{
if (listBarCodes==null||listBarCodes.Count < 1) return "";
XmlDocument doc = new XmlDocument();
XmlElement element = doc.CreateElement("BarCodes");
XmlElement xe = null;
doc.AppendChild(element);
var rootNode = doc.SelectSingleNode("BarCodes");
foreach (var BarCode in listBarCodes)
{
xe = doc.CreateElement("Details");
xe.SetAttribute("cBarCode", BarCode.cBarCode);
xe.SetAttribute("iQuantity", BarCode.iQuantity.ToString());
xe.SetAttribute("iRowNo", BarCode.iRowNo.ToString());
xe.SetAttribute("cWhCode", string.IsNullOrEmpty(BarCode.cWhCode)?"":BarCode.cWhCode);
xe.SetAttribute("cPosCode", string.IsNullOrEmpty(BarCode.cPosCode) ? "" : BarCode.cPosCode);
xe.SetAttribute("SkinWeight", BarCode.SkinWeight==null?"0":BarCode.SkinWeight.ToString());
xe.SetAttribute("GrossWeight", BarCode.SkinWeight == null ? "0" : BarCode.SkinWeight.ToString());
rootNode.AppendChild(xe);
}
return doc.OuterXml;
}
一：往xml中插入节点
DECLARE @xmlDoc XML;DECLARE @detail xml;
SET @xmlDoc='<XML></XML>'
SET @detail='<book id="001"><price>30</price></book>';
SET @xmlDoc.modify('insert sql:variable("@detail") into (/XML)[1] ')
SELECT @xmlDoc
--<XML><book id="001"><price>30</price></book></XML>
SET @detail='<book id="002"><price>50</price></book>';
SET @xmlDoc.modify('insert sql:variable("@detail") into (/XML)[1] ')
SELECT @xmlDoc
--<XML><book id="001"><price>30</price></book><book id="002"><price>50</price></book></XML>
二：xml传入数据库处理
DECLARE @xmlData XML,@xmlHead xml, @Pointer INT;
--XML字符串
SELECT @xmlHead='<Head>
<Details cCode="ABCD" cRdCode="0101" cDepcode="123" />
</Head>';
SELECT @xmlData='<Body>
<Details iRowNo="1" cWhCode="W" cBatch="0325" cMaker="宋世佳" cBarCode="DH19020063,1,102030035,2000.000000,C20181230,18123081,2021-12-29" BarCodeNo="00001" cInvCode="102030035" cOldPosCode="00000" cNewPosCode="WH1F-A001" iQuantity="2000" />
</Body>'
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xmlData;
--临时表接收数据
create table #Body
(
iRowNo int,
cWhCode varchar(50),
cMaker nvarchar(50),
cInvCode varchar(50),
cBatch varchar(50),
cOldPosCode varchar(50),
cNewPosCode varchar(50),
cBarCode varchar(300),
iQuantity INT,
BarCodeNo NVARCHAR(500)
)
;
INSERT INTO #Body( iRowNo, cWhCode, cMaker, cInvCode, cBatch, cOldPosCode, cNewPosCode,
cBarCode, iQuantity, BarCodeNo )
SELECT iRowNo, cWhCode, cMaker, cInvCode, cBatch, cOldPosCode, cNewPosCode,
cBarCode, iQuantity, BarCodeNo
FROM
OPENXML(@Pointer, '/Body/Details')
with
(
iRowNo int,cWhCode varchar(50),cBatch varchar(50),
cMaker nvarchar(50),cInvCode varchar(50),cOldPosCode varchar(50),
cNewPosCode varchar(50), iQuantity FLOAT,cBarCode VARCHAR(300), BarCodeNo NVARCHAR(500)
)
;
EXECUTE sp_xml_removedocument @Pointer;
--第二个xml
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xmlHead;
CREATE TABLE #Head
(
cCode NVARCHAR(60),cRdCode NVARCHAR(60),cDepcode NVARCHAR(60)
)
INSERT INTO #Head
( cCode, cRdCode, cDepcode )
select
cCode, cRdCode, cDepcode
FROM
OPENXML(@Pointer, '/Head/Details')
with
(
cCode varchar(50),
cRdCode nvarchar(50),
cDepcode varchar(50)
)
;
EXECUTE sp_xml_removedocument @Pointer;
SELECT * FROM #Body
SELECT * FROM #Head
DROP TABLE #Body
DROP TABLE #Head
方便从程序传入多行处理，而且速度更快，在实际项目中发现如果传入的xml字符串太多可能导致mssql卡
浙公网安备 33010602011771号