SqlServer OpenXML函数忽略表中自增列

最新推荐文章于 2025-04-08 15:57:07 发布

原创最新推荐文章于 2025-04-08 15:57:07 发布 · 989 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#openxml #SQL Server #xml

SQL SERVER 数据库专栏收录该内容

85 篇文章

订阅专栏

本文探讨了在使用OpenXML打开XML文档时，处理含有自增列的表与不含自增列的表的区别，特别关注了自增列在不同情况下的表现差异，并通过代码示例展示了如何通过自定义列模式解决这一问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在使用openxml打开xml文档时，如果在with子句中指定以TableName进行架构信息表示时，如果指定表中存在自增列，

则这里的自增列将不会出现在openxml返回的最终行集中。

具体参照以下代码：

1、含有自增列的表

CREATE TABLE TestOpenxmlIdentity(
SomeID INT IDENTITY(1,1),
NAME VARCHAR(100)
)

INSERT INTO TestOpenxmlIdentity
(
-- SomeID -- this column value is auto-generated
NAME
)
SELECT 'jim'
UNION
SELECT 'tom'
UNION
SELECT 'jerry'

DECLARE @tmpXml NVARCHAR(MAX)=
CONVERT(NVARCHAR(MAX),
(SELECT *
FROM TestOpenxmlIdentity AS di
FOR XML PATH('row'),ROOT('root'))
)

SELECT @tmpXml

DECLARE @idoc INT,@prepareXml INT
BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT,@tmpXml
SET @prepareXml=1

SELECT *
FROM OPENXML (@idoc, '/root/row',2)
WITH TestOpenxmlIdentity
--(NAME VARCHAR(100),
-- Age INT,
-- ADDRESS VARCHAR(100)
--)
EXEC sp_xml_removedocument @idoc
SET @prepareXml=0
END TRY
BEGIN CATCH
/*
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
*/
IF(@prepareXml=1)
BEGIN
EXEC sp_xml_removedocument @idoc
END
END CATCH

执行结果

(3 行受影响)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<root><row><SomeID>1</SomeID><NAME>jerry</NAME></row><row><SomeID>2</SomeID><NAME>jim</NAME></row><row><SomeID>3</SomeID><NAME>tom</NAME></row></root>

(1 行受影响)

NAME
----------------------------------------------------------------------------------------------------
jerry
jim
tom

(3 行受影响)

可以看到返回结果集中，SomeID列丢失了。

那如果要想返回这个列，我们可以通过自己定义列模式来完成

CREATE TABLE TestOpenxmlIdentity(
SomeID INT IDENTITY(1,1),
NAME VARCHAR(100)
)

INSERT INTO TestOpenxmlIdentity
(
-- SomeID -- this column value is auto-generated
NAME
)
SELECT 'jim'
UNION
SELECT 'tom'
UNION
SELECT 'jerry'

DECLARE @tmpXml NVARCHAR(MAX)=
CONVERT(NVARCHAR(MAX),
(SELECT *
FROM TestOpenxmlIdentity AS di
FOR XML PATH('row'),ROOT('root'))
)

SELECT @tmpXml

DECLARE @idoc INT,@prepareXml INT
BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT,@tmpXml
SET @prepareXml=1

SELECT *
FROM OPENXML (@idoc, '/root/row',2)
WITH
--TestOpenxmlIdentity
(SomeID INT,
NAME VARCHAR(100)
)
EXEC sp_xml_removedocument @idoc
SET @prepareXml=0
END TRY
BEGIN CATCH
/*
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
*/
IF(@prepareXml=1)
BEGIN
EXEC sp_xml_removedocument @idoc
END
END CATCH

DROP TABLE TestOpenxmlIdentity

返回结果

(3 行受影响)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<root><row><SomeID>1</SomeID><NAME>jerry</NAME></row><row><SomeID>2</SomeID><NAME>jim</NAME></row><row><SomeID>3</SomeID><NAME>tom</NAME></row></root>

(1 行受影响)

SomeID NAME
----------- ----------------------------------------------------------------------------------------------------
1 jerry
2 jim
3 tom

(3 行受影响)

2、不含有自增列的表

CREATE TABLE TestOpenxmlIdentity(
SomeID INT,
NAME VARCHAR(100)
)

INSERT INTO TestOpenxmlIdentity
(
SomeID, -- this column value is auto-generated
NAME
)
SELECT 1, 'jim'
UNION
SELECT 2,'tom'
UNION
SELECT 3,'jerry'

DECLARE @tmpXml NVARCHAR(MAX)=
CONVERT(NVARCHAR(MAX),
(SELECT *
FROM TestOpenxmlIdentity AS di
FOR XML PATH('row'),ROOT('root'))
)

SELECT @tmpXml

DECLARE @idoc INT,@prepareXml INT
BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT,@tmpXml
SET @prepareXml=1

SELECT *
FROM OPENXML (@idoc, '/root/row',2)
WITH
TestOpenxmlIdentity
--(SomeID INT,
-- NAME VARCHAR(100)
--)
EXEC sp_xml_removedocument @idoc
SET @prepareXml=0
END TRY
BEGIN CATCH
/*
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
*/
IF(@prepareXml=1)
BEGIN
EXEC sp_xml_removedocument @idoc
END
END CATCH

DROP TABLE TestOpenxmlIdentity

执行结果：

(3 行受影响)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<root><row><SomeID>1</SomeID><NAME>jim</NAME></row><row><SomeID>2</SomeID><NAME>tom</NAME></row><row><SomeID>3</SomeID><NAME>jerry</NAME></row></root>

(1 行受影响)

SomeID NAME
----------- ----------------------------------------------------------------------------------------------------
1 jim
2 tom
3 jerry

(3 行受影响)