分析 SQL Server 中的文本和特殊字符-解网

问：

我在 SQL Server 中使用 XML 中的特殊字符解析文本时遇到了问题。

假设我有一个 XML 文件，其中包含以下数据：Sample.xml

<People>
    <Person FirstName="Adam"
            LastName="Smith"
            Age="44"
            Weight="178">
        <Texts>
            <Text Country="US"
                  Language="EN"
                  TextType="1">&lt;div&gt;First sentence to retrieve.&lt;/div&gt;</Text>
            <Text Country="GB"
                  Language="EN"
                  TextType="2">&lt;div&gt;Second sentence to retrieve.&lt;/div&gt;</Text>
        </Texts>
    </Person>
</People>

我准备了以下 SQL 脚本，它可以解析除属性中的两个句子之外的所有内容：<TextType>

要检索的第一句话
要检索的第二句话

DECLARE @x XML
SELECT @x = f FROM OPENROWSET(BULK 'C:\Sample.xml', single_blob) AS C(f)
DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT * FROM OPENXML (@hdoc, '/People/Person/Texts/Text')
WITH (
        FirstName varchar(max) '../../@FirstName'
        , LastName varchar(max) '../../@LastName'
        , Age varchar(max) '../../@Age'
        , [Weight] varchar(max) '../../@Weight'
        , Country varchar(max) '@Country'
        , [Language] varchar(max) '@Language'
        , TextType varchar(max) '@TextType'
        )
EXEC sp_xml_removedocument @hdoc

你能帮我添加上面提到的句子的列吗？

SQL SQL Server XML 解析

在提出问题时，您需要提供一个最小的可重现示例：（1） DDL 和样本数据填充，即 CREATE 表加上 INSERT T-SQL 语句。（2）你需要做什么，即逻辑和你的代码尝试在T-SQL中实现它。（3）期望的输出，基于上面 #1 中的示例数据。（4）您的 SQL Server 版本（SELECT @@version;）。

答：

0赞 Charlieface 7/27/2022 #1

OPENXML很旧，基本上被弃用了，它有很多问题。

您应该使用较新的 XQuery 函数并检索数据。.nodes.value

您的主要问题是您将 XML 作为字符串存储在另一个 XML 中。您需要将其检索为，然后使用强制转换。nvarchar(max)TRY_CONVERT

SELECT 
    FirstName  = x1.Person.value('@FirstName', 'varchar(100)'),
    LastName   = x1.Person.value('@LastName' , 'varchar(100)'),
    Age        = x1.Person.value('@Age'      , 'int'),
    Weight     = x1.Person.value('@Weight'   , 'decimal(9,5)'),
    Country    = x2.Text.value('@Country' , 'char(2)'),
    [Language] = x2.Text.value('@Language', 'char(2)'),
    TextType   = x2.Text.value('@TextType', 'int'),
    value      = v.InnerXml.value('(div/text())[1]','nvarchar(max)')
FROM @x.nodes('People/Person') x1(Person)
CROSS APPLY x1.Person.nodes('Texts/Text') x2(Text)
CROSS APPLY (VALUES( TRY_CONVERT(xml, x2.Text.value('text()[1]','nvarchar(max)')) )) v(InnerXml);

db<>fiddle

请注意，有两个调用，一个调用馈送到下一个调用。.nodes

您甚至可以直接从OPENROWSET

SELECT 
    FirstName  = x1.Person.value('@FirstName', 'varchar(100)'),
    LastName   = x1.Person.value('@LastName' , 'varchar(100)'),
    Age        = x1.Person.value('@Age'      , 'int'),
    Weight     = x1.Person.value('@Weight'   , 'decimal(9,5)'),
    Country    = x2.Text.value('@Country' , 'char(2)'),
    [Language] = x2.Text.value('@Language', 'char(2)'),
    TextType   = x2.Text.value('@TextType', 'int'),
    value      = v.InnerXml.value('(div/text())[1]','nvarchar(max)')
FROM OPENROWSET(BULK 'C:\Sample.xml', single_blob) AS C(f)
CROSS APPLY (VALUES( TRY_CONVERT(xml, C.f) )) C2(AsXml)
CROSS APPLY C2.AsXml.nodes('People/Person') x1(Person)
CROSS APPLY x1.Person.nodes('Texts/Text') x2(Text)
CROSS APPLY (VALUES( TRY_CONVERT(xml, x2.Text.value('text()[1]','nvarchar(max)')) )) v(InnerXml);

我建议你修复生成这个XML的任何内容，理想情况下，你会在不字符串化的情况下传递内部XML。

上一个：用于将 XML 转换为表的最佳 ETL 工具

下一个：在没有循环的情况下将 Xml 数据转换为 sql server 中的表？

分析 SQL Server 中的文本和特殊字符

Parse text and special characters from SQL Server

评论