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