Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: Trouble declaring hierarchy using FOR XML EXPLICIT

    I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
    <group>
    <sourceid>
    <source></source>
    <id></id>
    <grouptype>
    <scheme></scheme>
    <typevalue></typevalue>
    </grouptype>
    <description>
    <short></short>
    <long></long>
    </descrption>
    </group>

    Here's my code:
    SELECT 1 as Tag,
    NULL as Parent,
    @p_school_desc as [sourceid!1!source!element],
    @p_term_code as [sourceid!1!id!element],
    NULL as [grouptype!1!scheme!element],
    NULL as [grouptype!1!typevalue!element],
    NULL as [description!1!short!element],
    NULL as [description!1!long!element]
    FROM course_main AS sourceid
    where sourceid.course_id = @p_course
    UNION
    SELECT 1 as Tag,
    NULL as Parent,
    NULL,
    NULL,
    @p_destination as [grouptype!2!scheme!element],
    'Term' as [grouptype!2!typevalue!element],
    NULL,
    NULL
    FROM course_main AS grouptype
    where grouptype.course_id = @p_course
    UNION
    SELECT 1 as Tag,
    NULL as Parent,
    NULL,
    NULL,
    NULL,
    NULL,
    @p_term_code as [description!2!short!element],
    @p_term_description as [description!2!long!element]
    FROM course_main AS grouptype
    where grouptype.course_id = @p_course
    FOR XML EXPLICIT

    I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14
    XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.


    Is there anyway to have two different child notes off of the same parent node?

    Thanks in advance!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    <group>
    <sourceid>
    <source></source>
    <id></id>
    <grouptype>
    <scheme></scheme>
    <typevalue></typevalue>
    </grouptype>
    <description>
    <short></short>
    <long></long>
    </descrption>
    </group>
    I am NOT the expert on XML by any means, but doesn't this violate good xml design fundamentals? First off, it's not well-formed document:

    1. sourceid has no closing tag
    2. the closing tag for description is not closed

    Even if it were a well-formed document, I am not certain that it follows best practices for XML documents. What is the document describing? A group? Shouldn't the root tag be <groups> to allow for multiple groups to be included in a single document?

    Just some thoughts...

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •