Databases
StackOverflow2013
Postsdbo
POExpanding list from SQL column with XML datatype
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExpanding list from SQL column with XML datatype
    text
    copied!<p>Given a table with an XML column, how can I expand a list stored therein, into a relational rowset?</p> <p>Here is an example of a similar table.</p> <pre><code>DECLARE @test TABLE ( id int, strings xml ) insert into @test (id, strings) select 1, '&lt;ArrayOfString&gt;&lt;string&gt;Alpha&lt;/string&gt;&lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Charlie&lt;/string&gt;&lt;/ArrayOfString&gt;' union select 2, '&lt;ArrayOfString&gt;&lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Delta&lt;/string&gt;&lt;/ArrayOfString&gt;' </code></pre> <p>I would like to obtain a rowset like this.</p> <pre><code>id string -- ------ 1 Alpha 1 Bravo 1 Charlie 2 Bravo 2 Delta </code></pre> <p>I have figured out that I can use the nodes method to output the XML value for each id like this</p> <pre><code>select id, R.strings.query('/ArrayOfString/string') as string from @test cross apply strings.nodes('/ArrayOfString/string') as R(strings) </code></pre> <p>returning</p> <pre><code>id string -- ------ 1 &lt;string&gt;Alpha&lt;/string&gt;&lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Charlie&lt;/string&gt; 1 &lt;string&gt;Alpha&lt;/string&gt;&lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Charlie&lt;/string&gt; 1 &lt;string&gt;Alpha&lt;/string&gt;&lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Charlie&lt;/string&gt; 2 &lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Delta&lt;/string&gt; 2 &lt;string&gt;Bravo&lt;/string&gt;&lt;string&gt;Delta&lt;/string&gt; </code></pre> <p>and that I can append [1] to the query to get only the first string</p> <pre><code>select id, R.strings.query('/ArrayOfString/string[1]') as string from @test cross apply strings.nodes('/ArrayOfString/string') as R(strings) </code></pre> <p>but that still leaves me with this</p> <pre><code>id string -- ------ 1 &lt;string&gt;Alpha&lt;/string&gt; 1 &lt;string&gt;Alpha&lt;/string&gt; 1 &lt;string&gt;Alpha&lt;/string&gt; 2 &lt;string&gt;Bravo&lt;/string&gt; 2 &lt;string&gt;Bravo&lt;/string&gt; </code></pre> <p>Additionally, if I change the <code>R.strings.query</code> to <code>R.strings.value</code> to get rid of the tags, I get an error; <strong>XQuery [@test.strings.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'</strong>.</p> <p>Can anyone point out what I'm missing in order to get each subsequent string value for each id on subsequent rows, and why the value() method is giving me that error?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload