Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: generating nested XML

    Hi,

    I must produce an XML file with this layout:
    Code:
    <root>
      <dataroot>
        <Professions>
          <Profession>web designer</Profession>
          <tags>
              <tag>Ruby on Rails</tag>
              <tag>Apache</tag>
              <tag>HTML/CSS</tag>
          </tags>
        </Professions>
        <Professions>
          <Profession>developer</Profession>
          <tags>
              <tag>.Net</tag>
              <tag>C#</tag>
          </tags>
        </Professions>
      </dataroot>
    </root>
    But my query produces:
    Code:
    <root>
      <dataroot>
        <Professions>
          <Profession>web designer</Profession>
          <tags>
            <tag>Ruby on Rails</tag>
          </tags>
          <tags>
            <tag>Apache</tag>
          </tags>
          <tags>
            <tag>HTML/CSS</tag>
          </tags>
        </Professions>
        <Professions>
          <Profession>developer</Profession>
          <tags>
            <tag>.Net</tag>
          </tags>
          <tags>
            <tag>C#</tag>
          </tags>
        </Professions>
      </dataroot>
    </root>
    These are the (simplified) tables:
    Code:
    DROP TABLE dbo.prof;
    DROP TABLE dbo.tags;
    --table of professions
    CREATE TABLE dbo.prof(
    	id		int	NOT NULL,
    	name		nvarchar(255)	NOT NULL,
    	CONSTRAINT pk_profession PRIMARY KEY (id)
    );
    INSERT INTO prof(id, name) VALUES(1, 'web designer'), (2, 'developer');
    
    --table with tags on professions
    CREATE TABLE dbo.tags(
    	id		int	IDENTITY(1, 1)	NOT NULL,
    	prof_id	int	NOT NULL,
    	tag		NVARCHAR(255)	NOT NULL,
    	CONSTRAINT pk_tags PRIMARY KEY (id)
    );
    INSERT INTO tags(prof_id, tag) VALUES
    (1, 'Ruby on Rails'), (1, 'Apache'), (1, 'HTML/CSS'),
    (2, '.Net'), (2, 'C#');
    This is the query that produces the XML:
    Code:
    select	
    		(select	B.name as [Profession],
    				(select TT.tag as [tag]
    				FROM dbo.tags as TT
    				WHERE TT.prof_id = B.id
    				FOR XML RAW('tags'), TYPE, ELEMENTS
    				) --as tags
    		FROM dbo.prof as B
    		FOR XML PATH ('Professions'), TYPE
    		) 
    FOR XML PATH ('dataroot'), ROOT ('root')
    I can't get my head around it
    I've even tried to solve it by using FOR XML RAW, imagine how desperate I must be.

    (Query must run in MSSQL 2005)
    Last edited by Wim; 10-30-14 at 05:06.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I had to do something similar for my job a while back. I have amended you query which *should* produce the results for what you are looking. More or less, it is changing the second FOR XML call slightly to define a root and elements.

    Code:
    select	
    		(select	B.name as [Profession],
    				(select TT.tag as [tag]
    				FROM dbo.tags as TT
    				WHERE TT.prof_id = B.id
    				FOR XML PATH ('tag'), ROOT ('tags'), TYPE
    				) --as tags
    		FROM dbo.prof as B
    		FOR XML PATH ('Professions'), TYPE
    		) 
    FOR XML PATH ('dataroot'), ROOT ('root')

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hi Brian,

    Thank you for your feedback. This is the result the script generates:
    Code:
    <root>
      <dataroot>
        <Professions>
          <Profession>web designer</Profession>
          <tags>
            <tag>
              <tag>Ruby on Rails</tag>
            </tag>
            <tag>
              <tag>Apache</tag>
            </tag>
            <tag>
              <tag>HTML/CSS</tag>
            </tag>
          </tags>
        </Professions>
        <Professions>
          <Profession>developer</Profession>
          <tags>
            <tag>
              <tag>.Net</tag>
            </tag>
            <tag>
              <tag>C#</tag>
            </tag>
          </tags>
        </Professions>
      </dataroot>
    </root>
    Unfortunately that is not the expected result

    I found the solution:
    Code:
    select	
    		(select	B.name as [Profession],
    				(select TT.tag as [tag]
    				FROM dbo.tags as TT
    				WHERE TT.prof_id = B.id
    				FOR XML PATH(''), TYPE
    				) as [tags]
    		FROM dbo.prof as B
    		FOR XML PATH ('Professions'), TYPE
    		) 
    FOR XML PATH ('dataroot'), ROOT ('root')
    It gives this result:
    Code:
    <root>
      <dataroot>
        <Professions>
          <Profession>web designer</Profession>
          <tags>
            <tag>Ruby on Rails</tag>
            <tag>Apache</tag>
            <tag>HTML/CSS</tag>
          </tags>
        </Professions>
        <Professions>
          <Profession>developer</Profession>
          <tags>
            <tag>.Net</tag>
            <tag>C#</tag>
          </tags>
        </Professions>
      </dataroot>
    </root>
    Brian, thank you anyway for having taken the time to respond to my question.
    Last edited by Wim; 10-30-14 at 14:11.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Yeah, I went back and found this as well. Truth that the devil is in the details.

Tags for this Thread

Posting Permissions

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