Databases
StackOverflow2013
Postsdbo
POGenerate Recursive XML using SQL Server 2005
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerate Recursive XML using SQL Server 2005
    primarykey
    data
    text
    <p>I am attempting to have SQL Server build an XML document using data that is located in multiple tables. For instance my tables could look like this:</p> <pre><code>TableKey | ParentKey | TableName | Fields 1-1-1-1 | null | Fruit | protein, calories 1-1-1-2 | 1-1-1-1 | Apples | somefield, largeapple, redorgreen 1-1-1-3 | 1-1-1-2 | CanApples | SomeCanAppleField, AnotherField 1-1-1-4 | 1-1-1-1 | Oranges | citric, calories 1-1-1-5 | 1-1-1-1 | Grapes | citric, calories, large 1-1-1-6 | 1-1-1-3 | CranCanApples | verybig 1-1-1-7 | 1-1-1-5 | WildGrapes | somemorefields, large, heavy, huge </code></pre> <p>The table above is an example of the type of data I have to work with. The fields are randomly named because the actual project does not have a finite number of fields or tables known at the time of running.</p> <p>I can run any one query and have it return the XML representation of that table, for instance:</p> <p>SELECT protein, calories FROM Fruit ORDER BY DateUpdated DESC FOR XML Path('')</p> <p>I need a function that recursively builds the XML document including the substructures using the ParentKey and ChildKey for depth.</p> <p>For instance, I would need the following string that I can execute returned from the function:</p> <pre><code>SELECT protein, calories,(SELECT somefield,largeapple,redorgreen,(SELECT SomeCanAppleField, AnotherField,(SELECT verybig FROM CranCanApples FOR XML AUTO, TYPE, ELEMENTS) FROM CanApples FOR XML AUTO, TYPE, ELEMENTS) FROM Apples FOR XML AUTO, TYPE, ELEMENTS) FROM Fruit FOR XML AUTO, TYPE, ELEMENTS </code></pre> <p>I would like to have a script that uses the <code>ParentKeys</code> and <code>TableKeys</code> to place these structures inside each other in XML, for instance:</p> <pre><code>&lt;Fruit&gt; &lt;protein&gt;123&lt;/protein&gt; &lt;calories&gt;500&lt;/calories&gt; &lt;Apples&gt; &lt;somefield&gt;true&lt;/somefield&gt; &lt;largeapple&gt;false&lt;/largeapple&gt; &lt;redorgreen&gt;green&lt;/redorgreen&gt; &lt;CanApples&gt; &lt;SomeCanAppleField&gt;false&lt;SomeCanAppleField&gt; &lt;AnotherField&gt;false&lt;/AnotherField&gt; &lt;CranCanApples&gt; &lt;verybig&gt;false&lt;/verybig&gt; &lt;/CranCanApples&gt; &lt;/CanApples&gt; &lt;/Apples&gt; &lt;Oranges&gt; &lt;citric&gt;true&lt;/citric&gt; &lt;calories&gt;232&lt;/calories&gt; &lt;/Oranges&gt; </code></pre> <p>..... and so on for each child table</p> <p>Can someone explain how to go about performing this action using a script? The XML structure depth varies based on the tables for that topic, i.e. "Fruit", "Animals", "Cars", "Computers"</p> <p>EDIT:</p> <p>This is what I have come up with so far but it does not work because of the EXEC</p> <pre><code>set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetSubTree](@TableKey varchar(36)) RETURNS XML BEGIN DECLARE @TableFields varchar(max), @TableName varchar(100), @TableKey2 varchar(36), @TableShortName varchar(50), @test xml SELECT @TableFields = 'SELECT '+TableFields, @TableName=TableName, @TableKey2=TableKey, @TableShortName=TableShortName FROM ListTableFields WHERE ParentKey=@TableKey OR (ParentKey ='' AND @TableKey IS NULL) SET @TableFields = replace(@TableFields,'SELECT ,','SELECT ') SET @TableName = replace(@TableName,'ReportMain','Report') SET @TableShortName = replace(@TableShortName,'Main',@TableName) RETURN ( EXEC(@TableFields+',dbo.GetSubTree('''+@TableKey2+''') FROM '+@TableName +' ORDER BY DateUpdated FOR XML PATH('''+@TableShortName+'''),TYPE') ) END </code></pre> <p>It turns out that I cannot use the EXEC command within a function but I "must" have a dynamic query as the fields and tablenames will always be different depending on the table.</p> <p>To initiate this I would use: </p> <pre><code>SELECT dbo.GetSubTree(null) FOR XML PATH(''),TYPE </code></pre> <p>Outside of the function this query does what I need.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USgcerkez
    1. USgcerkez
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POGenerate Recursive XML using SQL Server 2005
      1. USGuillermo Gutiérrez
      1. VTFavorite
    1. COI could really use some help on this. My primary language is Java and C. This just seems completely out of my element. After numerous google searches I have come across very little helpful information.
      singulars
      1. POGenerate Recursive XML using SQL Server 2005
      1. USgcerkez
    2. COYou can't use EXEC nor EXECUTE inside an user defined function.
      singulars
      1. POGenerate Recursive XML using SQL Server 2005
      1. USGuillermo Gutiérrez
 

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