T-SQL add delimiter between data when using OPENROWSET and XML

Question

I'm trying to add a delimiter between the data that is being combined in a single column and having a lot of trouble trying to do so.

I'm working from a large XML file (8+ megs) and trying to get pieces of the data combined down to single columns for my database. The below query is doing this (sort of) but there is no separation of the data currently.

WITH XmlFile (Contents) AS (
SELECT CONVERT (XML, BulkColumn) 
FROM OPENROWSET (BULK 'C:\test.xml', SINGLE_BLOB) AS XmlData
)
SELECT c.value('(name)[1]', 'varchar(max)') AS [Name],
        c.value('(stuff)[1]', 'varchar(max)') AS [Stuff]       
FROM   XmlFile CROSS APPLY Contents.nodes ('(/root/item)') AS t(c)

test.xml file:

<root>
<item>
    <name>Something</name>
    <stuff>
        <note>Test 123</note>
        <note>Another Test</note>
    </stuff>
</item>
</root>

What it's returning:

enter image description here

Which is almost what I want! both note nodes have been merged into a single column... But I can't figure out howto add a delimiter between the values "Test 123 , Another Test"

Any help would be greatly appreciated!

roman · Accepted Answer · 2013-10-31 16:38:37Z

3

you can use XQuery, something like this:

select
    c.value('(name)[1]', 'varchar(max)') as [Name],
    nullif(
      replace(
        c.query('
          for $i in stuff/note/text()
          return concat($i, ",")
         '
        ).value('.', 'nvarchar(max)') + ','
      ,',,','')
    , ',')
from @data.nodes ('/root/item') as t(c)

sql fiddle demo

edited Oct 31, 2013 at 16:38

answered Oct 30, 2013 at 21:55

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

bfritz Over a year ago

Wow! this method ran through the 8 meg XML file in under 2 seconds! as the other solution (I originally used and posted by Kyle) took over an hour to finish! The only problem with this is when there is no data it's still creating a row with the delimiter in it "," instead of NULL.

Kyle Hale · Accepted Answer · 2013-10-30 21:45:03Z

One solution:

/* testing setup */

declare @t xml = '<root>
<item>
    <name>Something</name>
    <stuff>
        <note>Test 123</note>
        <note>Another Test</note>
    </stuff>
</item>
<item>
    <name>Something else</name>
    <stuff>
        <note>Test 345</note>
        <note>Another 678</note>
    </stuff>
</item>
</root>';

declare @handle int
EXEC sp_xml_preparedocument @handle OUTPUT, @t


select 
P.n.value('(name/text())[1]', 'varchar(50)') as name,
stuff ((
SELECT 

       ', ' + D.n.value('(text())[1]', 'varchar(50)')
FROM   @t.nodes('/root/item') AS O(n)
CROSS APPLY O.n.nodes('stuff/note') AS D(n) 

Where P.n.value('(name/text())[1]', 'varchar(50)') = O.n.value('(name/text())[1]', 'varchar(50)')
for xml path('')), 1, 2, '') as stuff
FROM   @t.nodes('/root/item') AS P(n)

Collectives™ on Stack Overflow

T-SQL add delimiter between data when using OPENROWSET and XML

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