Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Right, you have a multitude of issues to overcome with your sample!</p> <p>Firstly, I'll give you the answer, although note that in order to process hierarchically correctly it had to be a recursive function, so the test data you provided had to be created in permanent tables rather than temporary (simpler) and then I'll point out a few useful techniques that I used in it to solve the problem.</p> <pre><code>ALTER FUNCTION GetTasks (@ParentId varchar(255)= NULL) RETURNS XML BEGIN DECLARE @ReturnXML XML SELECT @ReturnXML = ( SELECT ( SELECT CONVERT(XML, --Main task start tag '&lt;'+master_t.TaskType+' Name="'+master_t.TaskName+'"&gt;'+ CONVERT(VARCHAR(MAX), ( SELECT dbo.GetTasks(master_t.TaskId), ( SELECT CONVERT(XML,'&lt;'+DetailName+'&gt;'+DetailValue+'&lt;/'+DetailName+'&gt;') FROM TASK_DETAILS t WHERE TaskId = master_t.TaskId FOR XML PATH(''),Type ), ( SELECT Name,Value,Type FROM TASK_PARAMETERS t WHERE TaskId=master_t.TaskId FOR XML PATH('Parameter'),Type ) 'Parameters' FOR XML PATH(''),Type ) ) + --Main task end tag '&lt;/'+master_t.TaskType+'&gt;' ) FROM TASK master_t WHERE --Effectively ignore the parentId field if it is not passed. ISNULL(ParentTaskId,'') = CASE WHEN @ParentId IS NULL THEN '' ELSE @ParentId END FOR XML PATH(''),Type ) 'TaskList' FOR XML PATH(''),Type ) RETURN @ReturnXML END GO </code></pre> <p>Call this function like this:</p> <pre><code>SELECT dbo.GetTasks(NULL) </code></pre> <p>Right the techniques that I think are worth noting are:</p> <p>a) You can manually create xml nodes by simply building them from strings- this is useful if the node names are in the table. The only thing you have to be aware of is that to put an open and closing tag around a block, you will probably have to convert the block to a string first, tack on the tags, and then convert the whole thing to xml (piecemeal wont work as the convert-to-xml function would expect you to provide well formed XML.</p> <p>b) You sometimes have to nest things in brackets to achieve a tag around all the subtags... An example makes this clearer:</p> <pre><code> SELECT TaskName FROM TASK t FOR XML PATH('SomeRoot') </code></pre> <p>would yield:</p> <pre><code>&lt;SomeRoot&gt; &lt;TaskName&gt;Get Report Parameters&lt;/TaskName&gt; &lt;/SomeRoot&gt; &lt;SomeRoot&gt; &lt;TaskName&gt;Loop Report Creation&lt;/TaskName&gt; &lt;/SomeRoot&gt; &lt;SomeRoot&gt; &lt;TaskName&gt;Report In Loop&lt;/TaskName&gt; &lt;/SomeRoot&gt; &lt;SomeRoot&gt; &lt;TaskName&gt;Get Email Addresses&lt;/TaskName&gt; &lt;/SomeRoot&gt; &lt;SomeRoot&gt; &lt;TaskName&gt;Loop Mail Creation&lt;/TaskName&gt; &lt;/SomeRoot&gt; &lt;SomeRoot&gt; &lt;TaskName&gt;Send Email In Loop&lt;/TaskName&gt; &lt;/SomeRoot&gt; </code></pre> <p>To get the "SomeRoot" to appear around it, you could do this:</p> <pre><code>SELECT ( SELECT TaskName FROM TASK t FOR XML PATH(''),Type ) FOR XML PATH('SomeRoot') </code></pre> <p>If the node name is static (Note the XML PATH('')<strong>,Type</strong>, which basically ensures that XML path returns XML type data for further processing and doesn't escape it)</p> <p>If the node name is NOT static, you're stuck with something like this, with the need to convert to and from string to make it work. </p> <pre><code>SELECT CONVERT(XML, '&lt;'+DynamicName+'&gt;' + CONVERT(VARCHAR(MAX), ( SELECT TaskName FROM TASK t FOR XML PATH(''),Type ) ) + '&lt;/'+DynamicName+'&gt;' ) FROM (SELECT 'Test' as DynamicName) a </code></pre> <p>c) Regarding your question about getting different children tags to appear on the same level, it's pretty trivial, and you just have to remember that the usual problem of multiple layers of select ceases to be a problem with xml as an xml select just returns a single xml object. You can then use XML PATH to combine those results in a tree too.</p> <p>e.g.</p> <pre><code>SELECT (SELECT top 1 * FROM TASK FOR XML PATH(''),Type), (SELECT top 1 * FROM TASK_DETAILS FOR XML PATH(''),Type) </code></pre> <p>Will return a single row with two columns but if you then apply XML PATH('') To the whole, you've combined them on the same level</p> <pre><code>SELECT (SELECT top 1 * FROM TASK FOR XML PATH(''),Type), (SELECT top 1 * FROM TASK_DETAILS FOR XML PATH(''),Type) FOR XML PATH('Root') </code></pre> <p>d) Column names are converted into nodes for you if by XML PATH. Attributes are quite easy in that you just give the column an alias which is the appropriate xsl path e.g. 'MyNodeName\@MyAttributeName' obviously this precludes attributes that are also dynamically named. For that, in this example, I just built the xml from strings again. This incidentally is why dynamic node names is such a bad idea-- you basically allow your routine to create new attribute names and node names via data in the table... this means that you can't create a decent schema for your routine as you don't know in advance what data might be in the table... </p> <p>Moving on :)</p> <p>So, given those building blocks, the easiest thing to do is work from the deepest level, and build it block by block, then combine like above.</p> <p>I did that for your query, and eventually realised that to make it work hierarchically (i.e. n-nested levels) I had to write is as a function returning XML, which is called passing the parentnode to it (so that the function knows what to filter the result set down to). This will die a horrible death if you hierarchy is ill-formed and circular.</p> <p>Okay- hopefully there's something in there you can work with. This is purely XML PATH() oriented solution - there are alternative XML methods which can be useful in different situations.</p>
    singulars
    1. This table or related slice is empty.
    1. POSql XML Path with different children
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USRunonthespot
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSql XML Path with different children
      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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    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