T-SQL get value from XML node if node exists and fits schema

Question

I'm trying to read data from a KML file source. Part of what I need to read is styling information. Different styling rules are declared at the root level as <Style> nodes, with various amounts of information in them. I am only interested in those styles that define both a line color and a polygon fill color. Here is my current approach:

Sample 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>

My SQL code:

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

The problem with this approach is that the @xml_data, as a whole, returns true on both the exist() methods, meaning the value() method throws an error when trying to retrieve rules from <Style id="default"> and <Style id="h1">.
I've also tried both the following snippets:

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)

and

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

However, both of the above produce the following error: 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

Vesper Annstas · Accepted Answer · 2019-03-11T21:45:45.170

You might find that it's more performant (not to mention easier to troubleshoot potential issues like missing data) to pull all of the XML into your table variable or temp table, then select from that table where desired column is not null.

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

I would happily do this, however the `.value()` method throws an exception when it attempts to pull data from a node that doesn't exist. If you have a work-around for this problem, I'd love to know it? — pinkie, Mar 11 '19 at 20:52
@pinkie Ah, I see how you were trying to do that now. The code I've updated my answer with tests successfully for me; granted, it's XML and not KML. Hope it helps! — Vesper Annstas, Mar 11 '19 at 21:49
You're absolutely right that this works - I was actually being lied to by Intellisense the whole time, and only upon actually executing the `ALTER PROC` did i realise i was battling with non-issues. thank you so much for all your help! — pinkie, Mar 11 '19 at 22:07
My pleasure! If you're using SSMS, Ctrl + Shift + R (or Edit / IntelliSense / Refresh Local Cache) can help keep IntelliSense honest in many cases. Cheers! — Vesper Annstas, Mar 11 '19 at 22:10

T-SQL get value from XML node if node exists and fits schema

1 Answers1