Need Some Assistance in Explicit Mode 
Author Message
 Need Some Assistance in Explicit Mode

Frank,

I believe I understand how the documentation imposes a parent/child shape on
the customers and orders example for Explicit mode.  I have a table in SQL
which already has parent/child relationship built-in; and am having trouble
getting Explicit mode to work with the existing columns imposing the shape.

The table in my application is "Folders", in which users can organize other
information from my application.  The user can arrange their Folders any way
they want, forming a tree structure (including a Folder within a Folder).
The Folders table already contains (I think/hope) the information needed to
shape the XML.  The PrimaryKey column is the row's identity property --
which I hope to use as the row's Tag.  The ParentFolder column contains the
ID of the Folder's Parent, or NULL if the folder is at the root level -- to
be used as the Parent tag.  The TreeSequence column maintains an ordinal for
each tree level such that "Order by TreeSequence" produces the Folders in
the order one would see in a fully-expanded tree.  There's other stuff; but,
let's just say there's a Description column which is the folder's name.


to get their specific folders):

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query Client = '0'>



SELECT PrimaryKey As Tag, ParentFolder As Parent, Description As
[Folders!1!Description] FROM Folders

    ORDER BY TreeSequence
FOR XML EXPLICIT, ELEMENTS

</sql:query>
</ROOT>

The closest I've gotten is the error message:  "Undeclared tag ID 18 used in
FOR XML EXPLICIT query."  (also in the attachment).  The PrimaryKey value of
18 is the identity of the first Folders row -- which is at the root of the
selected Client's tree.

BTW, my Folders table columns existed prior to any thoughts about SQL/XML,
and are only the ones which I need to efficiently access and maintain the
tree.  So, if this works, EXPLICIT mode's design fits beautifully.

Thanks for the help,
Phillip

begin 666 ExplicitErrorMsg.xml






`
end



Fri, 06 Sep 2002 03:00:00 GMT
 Need Some Assistance in Explicit Mode

Phillip

Quote:
> The table in my application is "Folders", in which users can organize
other
> information from my application.  The user can arrange their Folders any
way
> they want, forming a tree structure (including a Folder within a Folder).
> The Folders table already contains (I think/hope) the information needed
to
> shape the XML.  The PrimaryKey column is the row's identity property --
> which I hope to use as the row's Tag.  The ParentFolder column contains

the

The way I understand the EXPLICIT mode, in order to get the parent / child
thing working, you need to run a query which will produce one parent row and
several child rows.  If you have a master table and a detail table, the best
way of doing this would be to do a UNION with a select from the master table
and a select from the detail table.  The Tag and Parent are constants, set
in the select statements.

So you might have

select 1 as Tag, null as Parent, Description As [Folders!1!Description]
from Folders

union all
select 2, 1, F1.Description
from Folders F1
join Folders F2 on F1.ParentKey = F2.PrimaryKey

Hope this provides a starting point

Best regards,
James Carlyle

UK (44) 171 813 0665
www.xmltree.com - directory of XML content on the web



Sat, 07 Sep 2002 03:00:00 GMT
 Need Some Assistance in Explicit Mode
James,

Thank you for leading me back on the right path.  FYI, the "winning" SQL is
close to what you had:

 SELECT Depth + 1 As Tag, Depth As Parent, Name As [Folders!1!Name],
TreeSequence As [Folders!2!TreeSequence], id As [Folders!1!id] FROM Folders
 UNION
 SELECT Depth + 1, Depth, Name, TreeSequence, id FROM Folders
  ORDER BY TreeSequence
 FOR XML EXPLICIT, ELEMENTS

The area I misunderstood is that the "tag" is not an identifier of the
node/parent node, but an indication of the nesting level (in my tree, the
Depth column).

The next step is being able to support a hierarchy which has an arbitrarily
large depth.  The sample and the current design of EXPLICIT MODE allow
imposing a structure which is implied by the relationship between tables.
My single table, through its self-references to a parent row, can nest
Folders within Folders, . . .

I believe that what would be needed for SQLXML to accomplish this is
something like [Folders!*!Name] -- that the same meta-data applies
regardless of the nesting level.

Is there a way to accomplish a self-referencing table, with arbitrary
nesting?

Thanks for the help,
Phillip


Quote:
> Phillip

> > The table in my application is "Folders", in which users can organize
> other
> > information from my application.  The user can arrange their Folders any
> way
> > they want, forming a tree structure (including a Folder within a
Folder).
> > The Folders table already contains (I think/hope) the information needed
> to
> > shape the XML.  The PrimaryKey column is the row's identity property --
> > which I hope to use as the row's Tag.  The ParentFolder column contains
> the

> The way I understand the EXPLICIT mode, in order to get the parent / child
> thing working, you need to run a query which will produce one parent row
and
> several child rows.  If you have a master table and a detail table, the
best
> way of doing this would be to do a UNION with a select from the master
table
> and a select from the detail table.  The Tag and Parent are constants, set
> in the select statements.

> So you might have

> select 1 as Tag, null as Parent, Description As [Folders!1!Description]
> from Folders

> union all
> select 2, 1, F1.Description
> from Folders F1
> join Folders F2 on F1.ParentKey = F2.PrimaryKey

> Hope this provides a starting point

> Best regards,
> James Carlyle

> UK (44) 171 813 0665
> www.xmltree.com - directory of XML content on the web



Sat, 07 Sep 2002 03:00:00 GMT
 Need Some Assistance in Explicit Mode
Phillip

I don't see how your query could work.

Quote:
>  SELECT Depth + 1 As Tag, Depth As Parent, Name As [Folders!1!Name],
> TreeSequence As [Folders!2!TreeSequence], id As [Folders!1!id] FROM
Folders
>  UNION
>  SELECT Depth + 1, Depth, Name, TreeSequence, id FROM Folders
>   ORDER BY TreeSequence
>  FOR XML EXPLICIT, ELEMENTS

As far as I can see, both select statements in your query will return the
same rows.  Where is the ParentKey relationship implemented that you
mentioned in an earlier post?  There seems to be no join or where clause.

We were looking at roughly the same problem. AFAIAK, SQLXML does a fine job
at mapping the structure of a relational database to XML.  The fact that
SQLXML cannot support arbitrary depth of nesting is more to do with the fact
that the relational model does not allow Bill Of Materials type explosion
easily.  In other words, forgetting for a moment SQLXML, could you write a
query which will output the nesting of folders to an arbitrary depth using
plain Transact SQL?

If one wanted to output a nested structure using SQLXML, we were thinking
that a better way to do it would be linearly, using ID and IDREF for parent
folder relations.  This would be no problem from the SQL perspective, and
the nesting could take place using an XSLT transform.

Best regards,
James Carlyle

UK (44) 171 813 0665
www.xmltree.com - directory of XML content on the web



Sun, 08 Sep 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Explicit mode gives tag renaming error

2. Sorting in EXPLICIT Mode

3. doubt with explicit mode

4. explicit mode query tool

5. EXPLICIT mode when calling a stored proceedure?

6. Organizing the structure in Explicit mode

7. problems with EXPLICIT Mode - MSSQL XML

8. Questions...mostly about the EXPLICIT mode

9. Explicit Mode

10. TOP N in select clause causes error for EXPLICIT mode

11. Explicit MODE How I Set the criteria 3 Tables

12. Need Assistance: Clustering and constraints in a table


 
Powered by phpBB® Forum Software