将xml列中的数据插入到临时表中
我有一个看起来像的xml列
SET @XMLData = '<ArrayOfEntityNested xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.Bijak">
<EntityNested>
<Id xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto">1</Id>
<Date xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak">0001-01-01T00:00:00</Date>
<Description xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak">deesc</Description>
<Number xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak" i:nil="true" />
</EntityNested>
</ArrayOfEntityNested>'
我需要将XML中的数据插入到临时表中。
为此,我使用以下代码。但是它不起作用,也没有在临时表中插入任何数据。
--Variables Decleration
DECLARE @XMLData VARCHAR(MAX)
DECLARE @idoc INT
-- Creating Temporary Table
CREATE TABLE #TEMP_TABLE
(
REC_ID INT IDENTITY(1,1),
[Id] INT,
[Date] VARCHAR(50),
[Number] VARCHAR(50),
);
--Case 1
SET @XMLData = '<ArrayOfEntityNested xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.Bijak">
<EntityNested>
<Id xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto">1</Id>
<Date xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak">0001-01-01T00:00:00</Date>
<Number xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak" i:nil="true" />
</EntityNested>
</ArrayOfEntityNested>
'
--Reading Data from XML and inserting into Temp Table
EXECUTE sp_xml_preparedocument @idoc OUTPUT, @XMLData
INSERT INTO #TEMP_TABLE
SELECT *
FROM OpenXML(@idoc,'/ArrayOfEntityNested/EntityNested', 1)
WITH #TEMP_TABLE
EXECUTE sp_xml_removedocument @idoc
--Displaying data from Temp Table
SELECT * FROM #TEMP_TABLE
DROP TABLE #TEMP_TABLE;
但这不起作用,如果正确的xml格式如下所示:
SET @XMLData = '<ArrayOfEntityNested>
<EntityNested>
<Id>1</Id>
<Date>0001-01-01T00:00:00</Date>
<Description>deesc</Description>
<EmployeeId>2</EmployeeId>
<IsDeleted>false</IsDeleted>
<LoadingPermitTruckId>7541</LoadingPermitTruckId>
</EntityNested>
</ArrayOfEntityNested>'
然后就可以了。
请帮我。
-
首先-请 使用适当的数据类型! 如果您的源数据是XML-为什么不使用
XML
数据类型?另外,如果您
Date
的表中有一个-为什么不输入aDATE
或DATETIME
类型呢?而为什么是Number
一个VARCHAR(50)
??没有意义......
然后:您无需查看XML文档中存在的XML名称空间-但 必须 !
最后-我建议使用本机XQuery支持,而不是使用旧的,不建议使用的
sp_xml_preparedocument
/OpenXML
方法…。对我来说似乎更容易,更清晰…
用这个:
-- variable declaration DECLARE @XMLData XML -- creating temporary table CREATE TABLE #TEMP_TABLE ( REC_ID INT IDENTITY(1,1), [Id] INT, [Date] DATETIME2(3), [Number] INT );
然后使用适当的XQuery语句, 包括XML名称空间 来处理数据:
SET @XMLData = '<ArrayOfEntityNested xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.Bijak"> <EntityNested> <Id xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto">1</Id> <Date xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak">0001-01-01T00:00:00</Date> <Number xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak" i:nil="true" /> </EntityNested> <EntityNested> <Id xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto">42</Id> <Date xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak">2013-12-22T14:45:00</Date> <Number xmlns="http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak">373</Number> </EntityNested> </ArrayOfEntityNested>' ;WITH XMLNAMESPACES ('http://schemas.datacontract.org/2004/07/Gbms.Dto.Bijak' AS ns1, 'http://schemas.datacontract.org/2004/07/Gbms.Dto' AS ns2, 'http://schemas.datacontract.org/2004/07/Gbms.Dto.VirginBijak' AS ns3) INSERT INTO #TEMP_TABLE(ID, Date, Number) SELECT xc.value('(ns2:Id)[1]', 'int'), xc.value('(ns3:Date)[1]', 'DateTime2'), xc.value('(ns3:Number)[1]', 'int') FROM @XmlData.nodes('/ns1:ArrayOfEntityNested/ns1:EntityNested') AS xt(xc)