将xml列中的数据插入到临时表中

Question

我有一个看起来像的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>'

然后就可以了。

请帮我。

Answer 1

首先-请 使用适当的数据类型！ 如果您的源数据是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)