SQL 将 xml 列中的数据插入到临时表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20866333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert data from xml column into temp table
提问by Iraj
I have a xml column that look like
我有一个 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>'
I need insert data from the XML into a temp table.
我需要将 XML 中的数据插入到临时表中。
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;
But that doesn't work, if xml format correct might look like :
但这不起作用，如果 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>'
then it works.
那么它的工作原理。
Please help me.
请帮我。
回答by marc_s
First of all - please use appropriate data types!If your source data is XML - why aren't you using the XMLdatatype?
首先 - 请使用适当的数据类型！如果您的源数据是 XML - 为什么不使用XML数据类型？
Also, if you have a Datein your table - why isn't that a DATEor DATETIMEtype?? And why is the Numbera VARCHAR(50)??
另外，如果您Date的表中有 a - 为什么不是 aDATE或DATETIME类型？为什么是Numbera VARCHAR(50)??
Makes no sense......
没有意义......
Then: you're not looking at the XML namespaces that are present in the XML document - but you must!
那么：您不是在查看 XML 文档中存在的 XML 名称空间 - 但您必须！
At lastly - I would recommend using the native XQuery support instead of the legacy, deprecated sp_xml_preparedocument/ OpenXMLapproach....
最后 - 我建议使用原生 XQuery 支持而不是遗留的、不推荐使用的sp_xml_preparedocument/OpenXML方法......
Seems much easier, much clearer to me...
对我来说似乎更容易，更清晰......
Use this:
用这个：
-- 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
);
and then use proper XQuery statements, including the XML namespacesto handle the data:
然后使用适当的 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)
回答by RameezAli
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<OutLookContact>
<Contact FirstName="Asif" LastName="Ghafoor" EmailAddress1="[email protected]" />
<Contact FirstName="Rameez" LastName="Ali" EmailAddress1="[email protected]" />
</OutLookContact>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
DECLARE @Temp TABLE(FirstName VARCHAR(250),LastName VARCHAR(250),Email1 VARCHAR(250))
INSERT INTO @Temp(FirstName,LastName,Email1)
SELECT *
FROM OPENXML (@idoc, '/OutLookContact/Contact',1)
WITH (FirstName varchar(50),LastName varchar(50),EmailAddress1 varchar(50))
select FirstName,LastName,Email1 from @Temp
回答by sasitharan
it will be a lot easier if you try to use a tool called pentaho. http://en.wikipedia.org/wiki/Pentahoit is an open source tool which is used for data integration.you can create a database connection from mysql or oracle to it and do the transformation.it is easy to use.
如果你尝试使用一个叫做 pentaho 的工具会容易很多。http://en.wikipedia.org/wiki/Pentaho它是一个开源工具，用于数据集成。您可以创建从 mysql 或 oracle 到它的数据库连接并进行转换。它易于使用。
