将 xml 列中的数据插入到临时表中 [英] insert data from xml column into temp table
问题描述
我有一个 xml 列,看起来像
I have a xml column that look like
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 中的数据插入到临时表中.
I need insert data from the XML into a temp table.
为此,我使用以下代码.但它不起作用,它没有向临时表中插入任何数据.
For this I use from following code. But it's not working, and it's not inserting any data into temp table.
--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 格式正确可能看起来像:
But that doesn't work, if xml format correct might look like :
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
数据类型?
First of all - please use appropriate data types! If your source data is XML - why aren't you using the XML
datatype?
此外,如果您的表中有 Date
- 为什么不是 DATE
或 DATETIME
类型??为什么 Number
是 VARCHAR(50)
??
Also, if you have a Date
in your table - why isn't that a DATE
or DATETIME
type?? And why is the Number
a VARCHAR(50)
??
毫无意义......
那么:您不是在查看 XML 文档中存在的 XML 名称空间 - 但您必须!
Then: you're not looking at the XML namespaces that are present in the XML document - but you must!
最后 - 我建议使用原生 XQuery 支持,而不是旧的、不推荐使用的 sp_xml_preparedocument
/OpenXML
方法....
At lastly - I would recommend using the native XQuery support instead of the legacy, deprecated sp_xml_preparedocument
/ OpenXML
approach....
对我来说似乎更容易,更清晰......
Seems much easier, much clearer to me...
使用这个:
-- 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 名称空间来处理数据:
and then use proper XQuery statements, including the XML namespaces to handle the data:
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)
这篇关于将 xml 列中的数据插入到临时表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!