如何用SQL移除XML标签的xsi:nil="true"属性并插入文本

需求说明 #

原XML内容：

<Header>
    <SampleTag>text</SampleTag>
    <GeographicalLimit xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
    <SampleTag> text </SampleTag>
</Header>

期望修改后：

<Header>
    <SampleTag>text</SampleTag>
    <GeographicalLimit>Sample text</GeographicalLimit>
    <SampleTag> text </SampleTag>
</Header>

SQL Server 解决方案 #

SQL Server的XML类型支持.modify()方法，可直接操作XML节点和属性：

-- 初始化测试XML数据（需带上xsi命名空间声明，否则无法识别xsi:nil属性）
DECLARE @XmlData XML = N'
<Header>
    <SampleTag>text</SampleTag>
    <GeographicalLimit xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
    <SampleTag> text </SampleTag>
</Header>
';

-- 移除xsi:nil属性
SET @XmlData.modify('
    declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
    replace value of (/Header/GeographicalLimit/@xsi:nil)[1] with ()
');

-- 往GeographicalLimit节点插入示例文本
SET @XmlData.modify('
    insert text{"Sample text"} into (/Header/GeographicalLimit)[1]
');

-- 输出修改后的XML
SELECT @XmlData AS ModifiedXml;

PostgreSQL 解决方案 #

PostgreSQL使用xmlmodify()函数处理XML修改：

WITH xml_source AS (
    SELECT '<Header>
    <SampleTag>text</SampleTag>
    <GeographicalLimit xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
    <SampleTag> text </SampleTag>
</Header>'::XML AS raw_xml
)
SELECT
    -- 先删除xsi:nil属性，再替换节点文本
    xmlmodify('delete /Header/GeographicalLimit/@xsi:nil',
    xmlmodify('replace value of /Header/GeographicalLimit/text() with "Sample text"', raw_xml)) AS modified_xml
FROM xml_source;

内容的提问来源于stack exchange，提问作者Madhavan