葡京网投哪个正规 > 联系我们 > mssqlserver中读取xml文件技巧【葡京网投哪个正规】

原标题:mssqlserver中读取xml文件技巧【葡京网投哪个正规】

浏览次数:88 时间:2020-05-08

OPENXML是二个rowset函数,它的行事措施临近于rowset函数OPENQUE中华VY和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="壹玖玖陆-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="1997-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 VARCHAPAJERO(40卡塔尔,ContactName VARCHA普拉多(40State of QatarState of Qatar OPENXML有八个参数: 第二个是sp_xml_preparedocument读取是的OUTPUT参数,在本示例中就是@hdoc; 第4个是两个XPath表明式,用来获得钦点地方的数目; 第多个是三个可选取,用来代表收获的点子,有0,1,2,8多样取值,详细表达请看 FROM前面包车型大巴WITH也是可选的,用来钦赐获取哪些数据字段,上边代码中只取了CustomerID和ContactName。上边的询问结果如下: CustomerID ContactName —————————————- —————————————- VINET Paul Henriot LILAS 卡洛斯 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,你必须要实施两项OPENQUEEscortY和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(36State of Qatar '../@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数据所急需的任何名字空间引得。注意在那边现身的别的UENCOREL都需求用尖括号括起来;

下列值是足以显示在这里列中以指明节点类型的值:

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'
)

代码如下复制代码

2 = 属性节点

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 = 实体节点

中央的本领是,将文件逐行按文件读取。然后把全数读取的行连接为叁个大的VARCHAMurano变量。最终,将变量传递给前方所说的代码。

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)节点的命名空间 U福睿斯I。假如值是 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(1000State of Qatar 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="CarlosGonzlez" Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="一九九六-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',2State of QatarWITH (OrderID int '../@OrderID', CustomerID varchar(10State of Qatar'../@CustomerID', OrderDate datetime '../@OrderDate', ProdID int '@ProductID', Qty int '@Quantity'State of Qatar 查询的结果为: OrderID CustomerID OrderDate ProdID Qty ———– ———- ———————– ———– ———– 10248 VINET 壹玖玖捌-07-04 00:00:00.000 11 12 10248 VINET 1999-07-04 00:00:00.000 42 10 10283 LILAS 一九九八-08-16 00:00:00.000 72 3 有时候XML中的数据并非以属性的不二诀窍存在,而是径直放在节点中,如下: 复制代码 代码如下: DECLARE @doc varchar(1000State of QatarSET @doc =' ROOT Customer CustomerID="VINET" ContactName="保罗 Henriot" Order OrderID10248/OrderID CustomerIDVINET/CustomerID EmployeeID5/EmployeeID OrderDate1997-07-04T00:00:00/OrderDate /Order /Customer /ROOT' 当时要获Order节点下的各样的值,能够用下边方法: 复制代码 代码如下: DECLARE @hdoc int DECLARE @doc varchar(1000State of Qatar SET @doc =' ROOT Customer CustomerID="VINET" ContactName="Paul Henriot" Order OrderID10248/OrderID CustomerIDVINET/CustomerID EmployeeID5/EmployeeID OrderDate一九九八-07-04T00:00:00/OrderDate /Order /Customer /ROOT' EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc SELECT * FROM OPENXML (@hdoc, '/ROOT/Customer/Order',1State of Qatar WITH (OrderID int 'OrderID', CustomerID varchar(10卡塔尔(قطر‎ 'CustomerID', EmployeeID int 'EmployeeID', OrderDate datetime 'OrderDate'卡塔尔 查询结果如下: OrderID CustomerID EmployeeID OrderDate ———– ———- ———– ———————– 10248 VINET 5 一九九七-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等别的操作。

本文由葡京网投哪个正规发布于联系我们,转载请注明出处:mssqlserver中读取xml文件技巧【葡京网投哪个正规】

关键词:

上一篇:没有了

下一篇:没有了