Day1 sql of Stack Exchange

OPENXML是一个rowset函数,它的工作方式类似于rowset函数OPENQUERY和OPENROWSET。使用OPENXML可以对XML数据执行JOINs操作而无需首先导入数据。你还可以将其同INSERT、SELECT、UPDATE以及DELETE等操作联合使用。

有时会在存储过程中处理一些XML格式的数据,所以会用到sp_xml_preparedocument,他可以将XML数据进行读取,然后使用
MSXML 分析器 (Msxmlsql.dll)
对其进行分析。我们就可以很容易的在存储过程中得到XML中我们想要的数据。下面的代码就是使用sp_xml_preparedocument读取XML:复制代码 代码如下: DECLARE @hdoc int DECLARE
@doc varchar(1000) SET @doc =’ ROOT Customer CustomerID=”VINET”
ContactName=”Paul Henriot” Order CustomerID=”VINET” EmployeeID=”5″
OrderDate=”1996-07-04T00:00:00″ OrderDetail OrderID=”10248″
ProductID=”11″ Quantity=”12″/ OrderDetail OrderID=”10248″ ProductID=”42″
Quantity=”10″/ /Order /Customer Customer CustomerID=”LILAS”
ContactName=”Carlos Gonzlez” Order CustomerID=”LILAS” EmployeeID=”3″
OrderDate=”1996-08-16T00:00:00″ OrderDetail OrderID=”10283″
ProductID=”72″ Quantity=”3″/ /Order /Customer /ROOT’ EXEC
sp_xml_preparedocument @hdoc OUTPUT, @doc
上面只是读取了XML,要想获取XML数据还需要使用OPENXML,代码如下: 复制代码 代码如下: SELECT * FROM
openxml(@hdoc,’/ROOT/Customer’,1) WITH (CustomerID
VARCHAR(40),ContactName VARCHAR(40)) OPENXML有三个参数:
第一个是sp_xml_preparedocument读取是的OUTPUT参数,在本示例中就是@hdoc;
第二个是一个XPath表达式,用来获取指定位置的数据;
第三个是一个可选项,用来表示获取的方式,有0,1,2,8四种取值,详细解释请看
FROM后面的WITH也是可选的,用来指定获取哪些数据字段,上面代码中只取了CustomerID和ContactName。上面的查询结果如下:
CustomerID ContactName —————————————- —————————————- VINET Paul Henriot
LILAS Carlos Gonzlez
如果不指定WITH子句,查询出来的是一个默认的表结构,如下:

1.Download StackExchange’s open data
https://archive.org/details/stackexchange

–DECLARE @xml varchar(1024) –SET @xml=’ –<root> — <code
id=”030001″> —  <point id=”01″ /> —  <point id=”02″ />
— </code> –</root>’ –DECLARE @hdoc int –EXEC
sp_xml_preparedocument @hdoc out,@xml –SELECT * FROM
–OPENXML(@hdoc, ‘/root/code/point’, 2)  –WITH  –(  —  itemCode
varchar(36) ‘../@id’, —  point varchar(36) ‘@id’ –) AS a —-在这里的
结果集 a 就包含了主从表的信息 —-INSERT INTO TABLEa SELECT * FROM a
–EXEC sp_xml_removedocument @hdoc

然而,要使用OPENXML,你必须执行两项OPENQUERY和OPENROWSET并不需要的任务。这两项任务需要两个系统存储进程。

表格列的解释说明:

2.Importing and Process data from XML files into SQL Server tables
https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

DECLARE @xml varchar(1024) SET @xml=’ <root>  <code
id=”030001″>   <point id=”01″ />   <point id=”02″ />
 </code> </root>’ DECLARE @hdoc int EXEC
sp_xml_preparedocument @hdoc out,@xml SELECT * FROM OPENXML(@hdoc,
‘/root/code/point’, 2)  WITH  (    itemCode varchar(36) ‘../@id’,  
point varchar(36) ‘@id’ ) AS a –在这里的 结果集 a 就包含了主从表的信息
–INSERT INTO TABLEa SELECT * FROM a EXEC sp_xml_removedocument @hdoc

第一个是sp_xml_preparedocument,它将读取特定的XML文本并将其内容提取到内存中。其语法如下:

列名

step1.Importing XML data from XML file using OPENROWSET
对于第一个脚本,第一个创建数据库的命令我选择了注释掉改为手动创建该数据库,因为使用该命令创建的权限方面貌似有点问题。这个阶段遇到的第二个问题是导入的xml文件过大,sql
server默认的配置是导入的xml文件有2M限制,在工具-选项里可以设置一下,设置完毕记得重启sql
server。虽然这个问题解决了,但在导入400M左右的xml文件时,sql
server报“System.OutOfMemoryException”的异常,猜测可能跟xml文件有关,因为在跑普通的table时,4G左右的表也没有出现这个问题,暂时先不解决了。

exec [dbo].SkyProcessXMLData   ‘ <DataSet>   <tblEmp>    
<name>Vishal</name>    
<designation>Developer</designation>l   </tblEmp>  
<tblEmp>     <name>Jibin</name>    
<designation>System Analyst</designation>l   </tblEmp>
</DataSet>’

代码如下复制代码

数据类型

step2.Process XML data using OPENXML function

 

sp_xml_preparedocument @hdoc = OUTPUT,

说明

First call the sp_xml_preparedocument stored procedure by
specifying the XML data which will then output the handle of the XML
data that it has prepared and stored in internal cache.Then we will use
the handle returned by the sp_xml_preparedocument stored procedure
in the OPENXML function to open the XML data and read it.
这个过程按照XML层级的不同以及自己数据提取需求的不同,要针对性的写属性,外部架构都是一样的,关键是属性提取那里,目录都是写到需要提取的最深层级,然后用’../’来返回上一级(父亲节点,对这里是以树的形式存储临时数据的)。”EXEC
sp_xml_removedocument @hDoc”语句用来释放内存。

 

[, @xmltext = ]

idbigint文档节点的唯一 ID。

step3.把查询结果存到新表中,以下是代码示例

CREATE PROCEDURE dbo.SkyProcessXMLData (  @xml XML ) AS BEGIN  DECLARE
@docHandle INT  EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xml
 SELECT    *D  FROM   OPENXML(@docHandle, ‘/DataSet/tblEmp’, 2)  WITH (
    name VARCHAR(50),designation VARCHAR(50)   )  EXECUTE
sp_xml_removedocument @docHandle END

[, @xpath_namespaces =

根元素的 ID 值为 0。保留负 ID 值。

USE Badges
GO

具体参数如下:

parentidbigint标识节点的父节点。此 ID
标识的父节点不一定是父元素。具体情况取决于此 ID
所标识节点的子节点的节点类型。例如,如果节点为文本节点,则其父节点可能是一个属性节点。

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

@hdoc:指向某内存区域的句柄,相关数据存放在这里。注意这是一个输出变量,当该进程运行后,该变量将包含指向XML文件内容在内存地址的句柄。由于你需要在随后使用此结果,因此要确保对其进行保存;

如果节点位于 XML 文档的顶层,则其 ParentID 为 NULL。

SELECT @XML = XMLData FROM XMLwithOpenXMLBadges

@xmltext:实际上你所希望处理的XML文本;

节点类型int标识节点类型,是对应于 XML 对象模型 (DOM)
节点类型编号的一个整数。

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

@xml_namespaces:为了正常操作你的XML数据所需要的任何名字空间索引。注意在这里出现的任何URL都需要用尖括号括起来;

下列值是可以显示在此列中以指明节点类型的值:

CREATE TABLE Badges
(
UserId varchar(50),
Name varchar(100),
Date datetime
);

假设所传递的这些参数都有效,并且XML文档存在,那么你的XML数据就会被存放到内存中去。现在你就可以调用sp_xml_preparedocument,传递存放有XML文件的变量,然后执行OPENXML。语法如下:

1 = 元素节点

INSERT INTO Badges
SELECT UserId,Name,Date
FROM OPENXML(@hDoc,’badges/row’)
WITH
(
UserId varchar ‘@UserId’,
Name varchar ‘@Name’,
Date datetime ‘@Date’
)

代码如下复制代码

2www.9778.com, = 属性节点

EXEC sp_xml_removedocument @hDoc
GO

OPENXML(idocint [in],rowpatternnvarchar[in],[flagsbyte[in]])

3 = 文本节点

[WITH (SchemaDeclaration | TableName)]

4 = CDATA 部分节点

注意:在本文中没有足够的文字来描述OPENXML所接收的参数。请参阅BOL以获取更多信息。在Transact-SQL
Reference中查找OPENXML。

5 = 实体引用节点

现在我们已经到达了最后的步骤。所有剩下的工作就是导入一个实际的XML文件到SQL并进行处理。

6 = 实体节点

基本的技巧是,将文件逐行按文本读取。然后把所有读取的行连接为一个大的VARCHAR变量。最后,将变量传递给前面所说的代码。

7 = 处理指令节点

以下就是读取文件并将其内容存放到某变量的代码:

8 = 注释节点

代码如下复制代码

9 = 文档节点

DECLARE @FileName varchar(255)

10 = 文档类型节点

DECLARE @ExecCmd VARCHAR(255)

11 = 文档片段节点

DECLARE @y INT

12 = 表示法节点

DECLARE @x INT

有关详细信息,请参阅 Microsoft XML (MSXML) SDK 中的“节点类型属性”主题。

DECLARE @FileContents VARCHAR(8000)

localnamenvarchar(max)提供元素或属性的本地名称。如果 DOM
对象没有名称,则为
NULL。prefixnvarchar(max)节点名称的命名空间前缀。namespaceurinvarchar(max)节点的命名空间
URI。如果值是
NULL,则命名空间不存在。datatypenvarchar(max)元素或属性行的实际数据类型,否则是
NULL。数据类型是从内联 DTD
中或从内联架构中推断得出。prevbigint前一个同级元素的 XML
ID。如果前面没有同级元素,则为
NULL。textntext包含文本形式的属性值或元素内容。如果边缘表项不需要值则为
NULL。

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine
VARCHAR(255))

在WITH子句中,我们还可以通过设置来获取父级元素的属性值:复制代码 代码如下: DECLARE @hdoc int DECLARE
@doc varchar(1000) SET @doc =’ ROOT Customer CustomerID=”VINET”
ContactName=”Paul Henriot” Order OrderID=”10248″ CustomerID=”VINET”
EmployeeID=”5″ OrderDate=”1996-07-04T00:00:00″ OrderDetail
ProductID=”11″ Quantity=”12″/ OrderDetail ProductID=”42″ Quantity=”10″/
/Order /Customer Customer CustomerID=”LILAS” ContactName=”Carlos
Gonzlez” Order OrderID=”10283″ CustomerID=”LILAS” EmployeeID=”3″
OrderDate=”1996-08-16T00:00:00″ OrderDetail ProductID=”72″ Quantity=”3″/
/Order /Customer /ROOT’ EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT * FROM OPENXML (@hdoc, ‘/ROOT/Customer/Order/OrderDetail’,2)
WITH (OrderID int ‘../@OrderID’, CustomerID varchar(10)
‘../@CustomerID’, OrderDate datetime ‘../@OrderDate’, ProdID int
‘@ProductID’, Qty int ‘@Quantity’) 查询的结果为: OrderID CustomerID
OrderDate ProdID Qty ———– ———- ———————– ———– ———– 10248 VINET 1996-07-04
00:00:00.000 11 12 10248 VINET 1996-07-04 00:00:00.000 42 10 10283 LILAS
1996-08-16 00:00:00.000 72 3
有时候XML中的数据并不是以属性的方式存在,而是直接放在节点中,如下: 复制代码 代码如下: DECLARE @doc varchar(1000)
SET @doc =’ ROOT Customer CustomerID=”VINET” ContactName=”Paul Henriot”
Order OrderID10248/OrderID CustomerIDVINET/CustomerID
EmployeeID5/EmployeeID OrderDate1996-07-04T00:00:00/OrderDate /Order
/Customer /ROOT’ 此时要获Order节点下的各项的值,可以用下面方法: 复制代码 代码如下: DECLARE @hdoc int DECLARE
@doc varchar(1000) SET @doc =’ ROOT Customer CustomerID=”VINET”
ContactName=”Paul Henriot” Order OrderID10248/OrderID
CustomerIDVINET/CustomerID EmployeeID5/EmployeeID
OrderDate1996-07-04T00:00:00/OrderDate /Order /Customer /ROOT’ EXEC
sp_xml_preparedocument @hdoc OUTPUT, @doc SELECT * FROM OPENXML
(@hdoc, ‘/ROOT/Customer/Order’,1) WITH (OrderID int ‘OrderID’,
CustomerID varchar(10) ‘CustomerID’, EmployeeID int ‘EmployeeID’,
OrderDate datetime ‘OrderDate’) 查询结果如下: OrderID CustomerID
EmployeeID OrderDate ———– ———- ———– ———————– 10248 VINET 5 1996-07-04
00:00:00.000
可以看出是取属性值还是取节点的文本的值区别在于WITH子句的第三个参数是否有@符号

SET @FileName = ‘C:TempCurrentSettings.xml’

SET @ExecCmd = ‘type ‘ + @FileName

SET @FileContents = ”

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

SELECT @y = count(*) from #tempXML

SET @x = 0

WHILE @x @y

BEGIN

SET @x = @x + 1

SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK

= @x

END

SELECT @FileContents as FileContents

DROP TABLE #tempXML

现在在变量@FileContents变量中你已经获得了文件的全部内容。所需要做的只是将变量通过@xmltext参数传递给sp_xml_preparedocument,然后再调用OPENXML。

有了这种解决办法,对XML文档进行各种处理就成为了可能。你可以将XML文档同SQL表格连接在一起而无需导入数据,然后对这些数据进行INSERT、PDATE和DELETE等任何操作。