Phil Cooper Phil Cooper - 2 months ago 8
SQL Question

Concatenating text of child nodes into a single column

I'm working with an API that returns property information. Some of the textual information is stored across child nodes and I'd like to concatenate it into a single string (VARCHAR).

My process is that I obtain the xml through a webservice, then pass this through to a proc which extracts the xml values and inserts them into a view, this is a snippet of the xml that I'm getting back:

<properties>
<property propertyid="1234">
<bullets>
<bullet>nice garden</bullet>
<bullet>it smells a bit</bullet>
<bullet>body under the patio</bullet>
</bullets>
</property>
...
</properties>


This is a glimpse into how the xml is being queried to extract values from it:

INSERT
INTO VProperty
( PropertyId,
Description
)
SELECT P.value('@propertyid', 'INT'),
NULL -- extract all the bullet text values into a single string
FROM @xml.nodes('/properties/property')


In this example, I'd like to be able to extract the information from the xml so it ends up like this:

PropertyId Description
1234 'nice garden\r\nit smells a bit\r\nbody under the patio


Is this going to be possible in pure sql/xml or am I going to need to perform some pre-processing on the xml before I enter SQL land?

Any help greatly appreciated (as always).

Answer

Does this work for you?

DECLARE @XML XML = 
('<properties>
    <property propertyid="1234">
        <bullets>
            <bullet>nice garden</bullet>
            <bullet>it smells a bit</bullet>
            <bullet>body under the patio</bullet>
        </bullets>
    </property>
    <property propertyid="2345">
        <bullets>
            <bullet>bullet 2345_1</bullet>
            <bullet>bullet 2345_2</bullet>
            <bullet>bullet 2345_3</bullet>
        </bullets>
    </property>
</properties>');

SELECT  X.node.value('@propertyid', 'INT'),
        STUFF((SELECT '\r\n' + B.bullet.value('.', 'NVARCHAR(MAX)')
               FROM   X.node.nodes('./bullets/bullet') B ( bullet )
               FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),
              1, 4, '') AS Description
FROM    @xml.nodes('/properties/property') X ( node );