SQLServer操作XML整理.docVIP

SQL操作XML整理 ===================================================================== SQL操作XML，实现导入功能 ----读取XML--------- declare @xml varchar(8000) set @xml=(SELECT * FROM OPENROWSET( BULK D:\peo.xml, SINGLE_CLOB) as x) select @xml Unicode 格式的文件用SINGLE_NCLOB. 读取XML alter PROCEDURE usp_InsertShoppingCartOrder ( @xml nvarchar(4000) ) AS BEGIN DECLARE @Pointer INT EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml INSERT INTO WebSales ( ProductID, Price, SaleDate, SaleBatchID, CustomerID ) SELECT ProductID,Price,SaleDate,SaleBatchID,CustomerID FROM OPENXML (@Pointer,/ShoppingCart/Purchase)WITH(ProductID INT,Price MONEY,SaleDate SMALLDATETIME,SaleBatchID INT, CustomerID INT ) EXEC sp_xml_removedocument @Pointer END 生成XML select * from users FOR XML RAW select * from users FOR XML auto select * from users FOR XML XPLICIT select * from users FOR XML auto,ELEMENTS 模式 说明 RAW 行集合的每个记录都转换成叫做行的XML元素。row元素将包含一个属性，用来表示所检索的列。 AUTO 行集合记录可以转换成以FROM子句中的表命名的嵌套XML元素。所检索每一列都将表示为一个属性 EXPLICIT 为格式化XML提供许多控制。不过，EXPLICIT模式的使用语法要复杂得多。XSLT是一个比较常用的XML转换方法。 结合net:语言 private string GetFORXML(string sFile, string sSQL) { // Create和open the connection to Northwind using(SqlConnection oCn = new SqlConnection(this.sCnNW)) { oCn.Open(); // Create the SQL command to execute SqlCommand oCmd = new SqlCommand(sSQL, oCn); DataSet oDs = new DataSet(); // Execute the SQL statement and return the data to an XmlReader. // Then read the schema and the fragment XmlReader oXml = oCmd.ExecuteXmlReader(); oDs.ReadXmlSchema(oXml); oDs.ReadXml(oXml, XmlReadMode.Fragment); oCn.Close(); oDs.DataSetName = root; oDs.WriteXml(this.sPath + sFile); return Wrote XML to file + sFile; } } 图片字段转换成BASE64读取： SELECT EmployeeID, FirstName, LastName, Photo FROM Employees WHERE LastName LIKE D% FOR XML RAW,BINARY BASE64 不支持聚合： 可以将聚合后的内容插入表变量中，对表变量处理XML DECLARE @TempTable TABLE (OrderID INT, Total