Extract XML element as a string from SQL Server XML Data Type

Question

I want to extract an element as a string from an XML. The method I am using is the following:

declare @xml xml = 
'<sl:stand xmlns:sl="http://www.kadaster.nl/schemas/standlevering-generiek/1.0">
    <sl-bag-extract:bagObject xmlns:sl-bag-extract="http://www.kadaster.nl/schemas/lvbag/extract-deelbestand-lvc/v20200601">
        <Objecten:Pand xmlns:Objecten="www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601">
            <Objecten:geometrie>
                <gml:Polygon srsName="urn:ogc:def:crs:EPSG::0"
                             srsDimension="3"
                             xmlns:gml="http://www.opengis.net/gml/3.2">
                    <gml:exterior>
                        <gml:LinearRing>
                            <gml:posList>1 2 3 4 5 6 1 2</gml:posList>
                        </gml:LinearRing>
                    </gml:exterior>
                </gml:Polygon>
            </Objecten:geometrie>
        </Objecten:Pand>
    </sl-bag-extract:bagObject>
</sl:stand>
'

;WITH XMLNAMESPACES('http://www.kadaster.nl/schemas/standlevering-generiek/1.0' AS sl,
                    'http://www.kadaster.nl/schemas/lvbag/extract-deelbestand-lvc/v20200601' AS [sl-bag-extract],
                    'www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601' AS Objecten,
                                        'www.kadaster.nl/schemas/lvbag/imbag/historie/v20200601' as Historie)
SELECT
Tab.Col.value('(Objecten:geometrie)[1]','varchar(max)') AS geometrie
FROM   @xml.nodes('/sl:stand/sl-bag-extract:bagObject/Objecten:Pand') Tab(Col)

The result that I will have from this query is:

1 2 3 4 5 6 1 2

But I expect it to be like this:

            <gml:Polygon srsName="urn:ogc:def:crs:EPSG::0"
                         srsDimension="3"
                         xmlns:gml="http://www.opengis.net/gml/3.2">
                <gml:exterior>
                    <gml:LinearRing>
                        <gml:posList>1 2 3 4 5 6 1 2</gml:posList>
                    </gml:LinearRing>
                </gml:exterior>
            </gml:Polygon>

How to extract an element as a string from this XML?

Tab.Col.query('Objecten:geometrie/node()') AS geometrie

lptr
– lptr

2021-04-15 08:11:17 +00:00
Commented Apr 15, 2021 at 8:11 — lptr
– lptr, Commented Apr 15, 2021 at 8:11

Thom A · Accepted Answer · 2021-04-15 08:12:02Z

2

Seems what you want is query not value:

WITH XMLNAMESPACES('http://www.kadaster.nl/schemas/standlevering-generiek/1.0' AS sl,
                    'http://www.kadaster.nl/schemas/lvbag/extract-deelbestand-lvc/v20200601' AS [sl-bag-extract],
                    'www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601' AS Objecten,
                    'www.kadaster.nl/schemas/lvbag/imbag/historie/v20200601' as Historie,
                    'http://www.opengis.net/gml/3.2' AS gml) --Needed to add this Namespace
SELECT Tab.Col.query('(Objecten:geometrie/gml:Polygon)[1]') AS geometrie
FROM   @xml.nodes('/sl:stand/sl-bag-extract:bagObject/Objecten:Pand') Tab(Col); --Statement terminators terminator your statements, not start them

answered Apr 15, 2021 at 8:12

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dejan Dozet Over a year ago

Thanks, to use query was the solution! And because I need inner elements I changed it to be like this: Tab.Col.query('(Objecten:geometrie/*)') as geometrie

Yitzhak Khabinsky · Accepted Answer · 2021-04-15 16:19:14Z

There is no need to use .nodes() method, just the .query() method is enough.

SQL

DECLARE @xml XML = 
N'<sl:stand xmlns:sl="http://www.kadaster.nl/schemas/standlevering-generiek/1.0">
    <sl-bag-extract:bagObject xmlns:sl-bag-extract="http://www.kadaster.nl/schemas/lvbag/extract-deelbestand-lvc/v20200601">
        <Objecten:Pand xmlns:Objecten="www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601">
            <Objecten:geometrie>
                <gml:Polygon srsName="urn:ogc:def:crs:EPSG::0"
                             srsDimension="3"
                             xmlns:gml="http://www.opengis.net/gml/3.2">
                    <gml:exterior>
                        <gml:LinearRing>
                            <gml:posList>1 2 3 4 5 6 1 2</gml:posList>
                        </gml:LinearRing>
                    </gml:exterior>
                </gml:Polygon>
            </Objecten:geometrie>
        </Objecten:Pand>
    </sl-bag-extract:bagObject>
</sl:stand>';

WITH XMLNAMESPACES('http://www.kadaster.nl/schemas/standlevering-generiek/1.0' AS sl,
                    'http://www.kadaster.nl/schemas/lvbag/extract-deelbestand-lvc/v20200601' AS [sl-bag-extract],
                    'www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601' AS Objecten,
                    'http://www.opengis.net/gml/3.2' AS gml)
SELECT @xml.query('/sl:stand/sl-bag-extract:bagObject/Objecten:Pand/Objecten:geometrie/gml:Polygon') AS geometrie;

Output

<gml:Polygon xmlns:gml="http://www.opengis.net/gml/3.2"
             srsName="urn:ogc:def:crs:EPSG::0" srsDimension="3">
    <gml:exterior>
        <gml:LinearRing>
            <gml:posList>1 2 3 4 5 6 1 2</gml:posList>
        </gml:LinearRing>
    </gml:exterior>
</gml:Polygon>

Collectives™ on Stack Overflow

Extract XML element as a string from SQL Server XML Data Type

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related