SQL Server 2008 - XML nodes.value 中的 sql_variant

Question

试图了解为什么在 SQL Server 中使用 XML 节点函数时不能将 sql_variant 用于值类型？我有一个场景，我将动态解析一些 XML 输入，并且能够使用 sql_variant 将是一个很好的替代方案，而不是必须分配数据类型变量或在 sys.columns 上进行查找。

例子:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlSourceTable]') AND type in (N'U'))
DROP TABLE [dbo].[XmlSourceTable]
GO
CREATE TABLE [dbo].[XmlSourceTable](
    [RecordId] [int] IDENTITY(1,1) NOT NULL,
    [XmlData] [xml] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [RecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DECLARE @XML XML ='<?xml version="1.0" ?>
<Root>
      <Person>
            <Name>Simon</Name>
            <Age>20</Age>
            <Skills>
                  <Skill>Cooking</Skill>
                  <Skill>Cleaning</Skill>
            </Skills>
      </Person>
      <Person>
            <Name>Peter</Name>
            <Age>21</Age>
            <Skills>
                  <Skill>Ironing</Skill>
            </Skills>
      </Person>
</Root>'

INSERT INTO XmlSourceTable(XmlData)
SELECT @XML

GO

SELECT * FROM XmlSourceTable
GO
SELECT
      pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,
      pref.value('(Age/text())[1]', 'int') as PersonAge,
      pref.query('Skills') as PersonSkills
FROM  
      XmlSourceTable CROSS APPLY
      XmlData.nodes('/Root/Person') AS People(pref)

所以而不是:

SELECT * FROM XmlSourceTable
GO
SELECT
      pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,
      pref.value('(Age/text())[1]', 'int') as PersonAge,
      pref.query('Skills') as PersonSkills
FROM  
      XmlSourceTable CROSS APPLY
      XmlData.nodes('/Root/Person') AS People(pref)

使用它会很好:

SELECT * FROM XmlSourceTable
GO
SELECT
      pref.value('(Name/text())[1]', 'sql_variant') as PersonName,
      pref.value('(Age/text())[1]', 'sql_variantt') as PersonAge,
      pref.query('Skills') as PersonSkills
FROM  
      XmlSourceTable CROSS APPLY
      XmlData.nodes('/Root/Person') AS People(pref)

但我收到此错误:
消息 9500，级别 16，状态 1，第 1 行
VALUE 方法中使用的数据类型“sql_variant)”无效。

无论如何要在节点函数中使用 sql_variant？

谢谢，

秒

Answer 1

sql_variant是一种元类型，它在值中存储数据的实际类型(int、char、float、date 等)。问题在于 XML 表达式没有类型:像 ('Name/text())[1] 这样的 XPath 表达式结果的类型是什么？ ?我知道您可能会争辩说，如果 XML 具有架构，则可以推断出与该节点/属性/元素关联的 xs 架构类型，但我想说，也许您可​​以推断出该类型，但仍然保留了绝大多数无模式 XML 文档正在使用中...这就是 .value() 的原因XML 方法需要一个类型，因此它可以将在 XML 中找到的文本强制转换为适当的类型。

值得注意的一件事是大多数 XML 值都可以提取为 VARCHAR ，它将给出该元素/属性的原始 XML 文本。然后，您可以将文本转换为适当的列。但我认为做你现在做的事情(查找列类型，使用正确的类型动态构建 XPath/类型)更好。