Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The answer to the question <a href="https://stackoverflow.com/a/3246587/864341">Sql XML Path with different children</a> contains a solution that should work for you.</p> <p>I don't fully understand the structure of the data returned by the query you specified. Therefore, I'll give you an example with a simpler structure, and you should be able to adapt it to fit your needs.</p> <p>Assuming you have an <code>UpdateEvent</code> table, with columns <code>UpdateEventKey</code>, <code>EventDateTime</code>, and <code>UpdateType</code>, the following query will give you what you want.</p> <p>Notice that the table is used twice, in an outer select to generate the variable tag names in the record nodes, and in a sub-select to generate the field nodes. Also, <code>PATH('')</code> is used to omit the record node (since this is already generated in the outer select).</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE UpdateEvent ( UpdateEventKey INT, EventDateTime DATETIME, UpdateType VARCHAR(50) ); INSERT INTO UpdateEvent (UpdateEventKey, EventDateTime, UpdateType) VALUES (1, GETDATE(), 'Comment'); INSERT INTO UpdateEvent (UpdateEventKey, EventDateTime, UpdateType) VALUES (2, GETDATE() + (1 / 1440.0), 'Attachment'); INSERT INTO UpdateEvent (UpdateEventKey, EventDateTime, UpdateType) VALUES (3, GETDATE() + (2 / 1440.0), 'Comment'); INSERT INTO UpdateEvent (UpdateEventKey, EventDateTime, UpdateType) VALUES (4, GETDATE() + (3 / 1440.0), 'OtherTypeOf'); SELECT CAST('&lt;' + U1.UpdateType + 'Update&gt;' + (SELECT * FROM UpdateEvent AS U2 WHERE U2.UpdateEventKey = U1.UpdateEventKey FOR XML PATH('')) + '&lt;/' + U1.UpdateType + 'Update&gt;' AS XML) FROM UpdateEvent AS U1 FOR XML PATH(''), ROOT('Updates'), TYPE; </code></pre> <p>This generates the following XML.</p> <pre class="lang-xml prettyprint-override"><code>&lt;Updates&gt; &lt;CommentUpdate&gt; &lt;UpdateEventKey&gt;1&lt;/UpdateEventKey&gt; &lt;EventDateTime&gt;2013-02-14T20:32:41.803&lt;/EventDateTime&gt; &lt;UpdateType&gt;Comment&lt;/UpdateType&gt; &lt;/CommentUpdate&gt; &lt;AttachmentUpdate&gt; &lt;UpdateEventKey&gt;2&lt;/UpdateEventKey&gt; &lt;EventDateTime&gt;2013-02-14T20:33:41.767&lt;/EventDateTime&gt; &lt;UpdateType&gt;Attachment&lt;/UpdateType&gt; &lt;/AttachmentUpdate&gt; &lt;CommentUpdate&gt; &lt;UpdateEventKey&gt;3&lt;/UpdateEventKey&gt; &lt;EventDateTime&gt;2013-02-14T20:34:41.727&lt;/EventDateTime&gt; &lt;UpdateType&gt;Comment&lt;/UpdateType&gt; &lt;/CommentUpdate&gt; &lt;OtherTypeOfUpdate&gt; &lt;UpdateEventKey&gt;4&lt;/UpdateEventKey&gt; &lt;EventDateTime&gt;2013-02-14T20:35:41.777&lt;/EventDateTime&gt; &lt;UpdateType&gt;OtherTypeOf&lt;/UpdateType&gt; &lt;/OtherTypeOfUpdate&gt; &lt;/Updates&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMixed elements and SQL for xml path
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USgmm
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMixed elements and SQL for xml path
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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