时间戳

首页 > 解决方案 > Extracting multiple values from a table column previously casted to XML

sql-server - Extracting multiple values from a table column previously casted to XML

问题描述

I have a table column as nvarchar(max)containing the following XML data:

<?xml version="1.0" encoding="utf-8"?>
<SerializableAlertDetail xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="SerializableContextAlertDetail">
  <ContextName>Evénements PTI mobile</ContextName>
  <EnumValueName>Pré Alerte immobilisme</EnumValueName>
</SerializableAlertDetail>

I would like to SELECT the concatenation of the 'ContextName' and 'EnumValueName' XML elements.

At first I simply tried to return one element, which works fine:

SELECT CAST(REPLACE(dbo.AlertDetail.Context, 'encoding="utf-8"', '') AS XML).value('(/SerializableAlertDetail/*[local-name() = "ContextName"])[1]', 'nvarchar(max)') As DisplayName FROM Table

Because I do not want to cast twice in the query, I'm looking for a way to destructure the XML column into a table and select columns from here. So far I'm stuck with the following invalid query:

SELECT T0.XML.value('ContextName', 'nvarchar(max)')
FROM Table c
    CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X)
    CROSS APPLY T.X.nodes('SerializableAlertDetail') AS T0(XML)

But it fails with the following error message:

XQuery [T.X.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Any help appreciated.

EDIT 1

I've come to the following query that works but may probably not be optimal:

SELECT T0.XML.query('./ContextName').value('.', 'nvarchar(max)') + T0.XML.query('./EnumValueName').value('.', 'nvarchar(max)')
FROM Table c
        CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X)
        CROSS APPLY T.X.nodes('SerializableAlertDetail') AS T0(XML)

EDIT 2

Replaced ntext by nvarchar(max) ;)

标签： sql-serverxmlxpathsqlxml

解决方案

你已经被告知，这已经NTEXT被弃用了几个世纪;-)

只是一些背景：

NTEXT是2 字节编码文本，将转换为NVARCHAR. 但是你的 XML 大喊我是 UTF-8 ！！！，这是一个明显的谎言。里面NTEXT是——当然！- 不是 UTF-8。有两种方法可以继续：

  • 将您的NTEXT转换为NVARCHAR，然后转换VARCHAR为 XML。这将起作用，因为 UTF-8 是用纯拉丁文编码的 1 字节。
  • utf-8用 1) 什么都没有（就像你做的那样），用 2)utf-16或 3) ucs-2（正确的东西）代替谎言来消除谎言。

关于您的查询：这可以更简单一些，因为没有重复的内容，因此不需要派生表。尝试这个：

SELECT X.value('(/SerializableAlertDetail/ContextName/text())[1]','nvarchar(max)') 
     + X.value('(/SerializableAlertDetail/EnumValueName/text())[1]','nvarchar(max)')
FROM Table c
        CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X);

这应该更快...

在可读性方面，您甚至可以尝试

SELECT X.value('(//ContextName)[1]','nvarchar(max)') 
     + X.value('(//EnumValueName)[1]','nvarchar(max)')
FROM Table c
        CROSS APPLY (SELECT CAST(REPLACE(c.Context, 'encoding="utf-8"', '') AS XML)) as T(X);

推荐阅读