时间戳

首页 > 解决方案 > 如果节点存在且适合模式，T-SQL 从 XML 节点获取值

sql - 如果节点存在且适合模式，T-SQL 从 XML 节点获取值

问题描述

我正在尝试从KML文件源读取数据。我需要阅读的部分内容是样式信息。不同的样式规则在根级别声明为<Style>节点，其中包含不同数量的信息。我只对定义线条颜色和多边形填充颜色的样式感兴趣。这是我目前的方法：

示例 xml：

<Document>
    <Style id="default">
    </Style>
    <Style id="hl">
        <IconStyle>
            <scale>1.2</scale>
        </IconStyle>
    </Style>
    <Style id="White-Style">
        <LineStyle>
            <color>FFFFFFFF</color>
        </LineStyle>
        <PolyStyle>
            <color>FFFFFFFF</color>
        </PolyStyle>
    </Style>
    <Style id="Black-Style">
        <LineStyle>
            <color>FF000000</color>
        </LineStyle>
        <PolyStyle>
            <color>FF000000</color>
        </PolyStyle>
    </Style>
    <Placemark> ... </Placemark>
    ...
</Document>

我的 SQL 代码：

declare @style table( style_id nvarchar(50), line_color nchar(8), fill_color nchar(8) )
;with xmlnamespaces('http://www.opengis.net/kml/2.2'AS K)
insert into @style
select
T.A.value('(@K:id)[1]', 'nvarchar(50)'),
T.A.value('/K:LineStyle[1]/K:color[1]/.', 'nchar(8)'),
T.A.value('/K:PolyStyle[1]/K:color[1]/.', 'nchar(8)')
from @xml_data.nodes('//K:Style') as T(A)
where @xml_data.exist('//K:Style/K:PolyStyle/K:color') = 1 and @xml_data.exist('//K:Style/K:LineStyle/K:color') = 1

这种方法的问题在于，作为一个整体，@xml_data 在两种方法上都返回 true，这exist()意味着该方法在尝试从和value()检索规则时会引发错误。 我还尝试了以下两个片段：<Style id="default"><Style id="h1">

select
...
(case T.A.exists('/K:LineStyle[1]/K:color') = 1 then T.A.value('/K:LineStyle[1]/K:color[1]/.', 'nchar(8)') else null end)

和

where T.A.exist('/K:PolyStyle[1]/K:color') = 1 and T.A.exist('/K:LineStyle[1]/K:color') = 1

但是，以上两种方法都会产生以下错误：The column 'A' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks

标签： sqlxmltsqlxqueryxquery-sql

解决方案

您可能会发现将所有 XML 提取到表变量或临时表中，然后从所需列不为空的表中进行选择，这样的性能更高（更不用说更容易解决丢失数据等潜在问题）。

DECLARE @StyleXML XML

SET @StyleXML = 
'<?xml version="1.0" encoding="UTF-8"?> 
    <Document>
        <Style id="default">
        </Style>
        <Style id="hl">
            <IconStyle>
                <scale>1.2</scale>
            </IconStyle>
        </Style>
        <Style id="White-Style">
            <LineStyle>
                <color>FFFFFFFF</color>
            </LineStyle>
            <PolyStyle>
                <color>FFFFFFFF</color>
            </PolyStyle>
        </Style>
        <Style id="Black-Style">
            <LineStyle>
                <color>FF000000</color>
            </LineStyle>
            <PolyStyle>
                <color>FF000000</color>
            </PolyStyle>
        </Style>
        <Placemark> ... </Placemark>
        ...
    </Document>'

SELECT
     T.A.value('(@id)[1]', 'nvarchar(50)')                  AS ID
    ,T.A.value('./LineStyle[1]/color[1]/.', 'varchar(8)')   AS LSColor
    ,T.A.value('./PolyStyle[1]/color[1]/.', 'varchar(8)')   AS PSColor
INTO #Style
FROM @StyleXML.nodes('Document/Style') AS T(A)

SELECT *
FROM #Style
WHERE LSColor IS NOT NULL
  AND PSColor IS NOT NULL

推荐阅读