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

    Answered: how to add addtributes to topmost XML element

    Hi,

    We have to export survey results in an XML-file.

    The XML my scripts generates is:
    Code:
    <Surveys2014>
      <Survey Course="20" SurveyDate="2015-01-30">
        <Question QuestionCode="Satisfaction" Score="2" />
        <Question QuestionCode="Recommend" Score="4" />
        <Question QuestionCode="Infrastructure" Score="3" />
      </Survey>
      <Survey Course="40" SurveyDate="2015-02-11">
        <Question QuestionCode="Satisfaction" Score="4" />
        <Question QuestionCode="Up-to-date" Score="1" />
        <Question QuestionCode="Infrastructure" Score="3" />
      </Survey>
    </Surveys2014>
    While it should be

    Code:
    <Surveys2014 school="1" file_generation_time="2015-03-10T10:42:49.350">
      <Survey Course="20" SurveyDate="2015-01-30">
        <Question QuestionCode="Satisfaction" Score="2" />
        <Question QuestionCode="Recommend" Score="4" />
        <Question QuestionCode="Infrastructure" Score="3" />
      </Survey>
      <Survey Course="40" SurveyDate="2015-02-11">
        <Question QuestionCode="Satisfaction" Score="4" />
        <Question QuestionCode="Up-to-date" Score="1" />
        <Question QuestionCode="Infrastructure" Score="3" />
      </Survey>
    </Surveys2014>
    But I don't know how to add those attributes to the <Surveys2014> element.

    This is my code, with a very simplified data model.

    Code:
    CREATE TABLE dbo.survey_package(
    	id				INT	IDENTITY(1, 1)	NOT NULL,
    	school_id			INT			NOT NULL,
    	file_generation_time	datetime	NOT NULL,
    	CONSTRAINT pk_dbo_survey_package PRIMARY KEY (id)
    )
    
    CREATE TABLE dbo.survey2014(
    	id				INT	IDENTITY(1, 1)	NOT NULL,
    	survey_package_id	INT			NOT NULL,
    	course_id			INT			NOT NULL,
    	survey_date		DATE		NOT NULL,
    	CONSTRAINT pk_dbo_survey2014 PRIMARY KEY (id)
    )
    
    CREATE TABLE dbo.survey2014_score(
    	id				INT	IDENTITY(1, 1)	NOT NULL,
    	survey2014_id		INT				NOT NULL,
    	survey_question		NVARCHAR(20)	NOT NULL,
    	survey_score		INT				NOT NULL,
    	CONSTRAINT pk_dbo_survey2014_score PRIMARY KEY (id)
    )
    
    INSERT INTO dbo.survey_package(school_id,file_generation_time)  
    VALUES (1, GetDate());
    
    INSERT INTO dbo.survey2014(survey_package_id,course_id,survey_date)VALUES 
    (1, 20, '20150130'),
    (1, 40, '20150211');
    
    INSERT INTO dbo.survey2014_score(survey2014_id,survey_question,survey_score)VALUES
    (1, 'Satisfaction', 2),
    (1, 'Recommend', 4),
    (1, 'Infrastructure', 3),
    
    (2, 'Satisfaction', 4),
    (2, 'Up-to-date', 1),
    (2, 'Infrastructure', 3);
    
    --SELECT * FROm survey_package;
    --SELECT * FROm survey2014;
    --SELECT * FROm survey2014_score;
    
    SELECT 
    	--SP.school_id [@school], 
    	--SP.file_generation_time [@file_generation_time],
    	survey.course_id as [@Course], 
    	survey.survey_date as [@SurveyDate], 
    	(SELECT SS.survey_question as [@QuestionCode], SS.survey_score as [@Score]
    	FROM dbo.survey2014_score as SS
    	WHERE SS.survey2014_id = survey.id
    	FOR XML PATH('Question'), TYPE
    	) 
    FROM dbo.survey_package AS SP
    	INNER JOIN dbo.survey2014 as survey ON
    		SP.id= survey.survey_package_id
    FOR XML PATH('Survey'), ROOT ('Surveys2014')
    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. Best Answer
    Posted by Wim

    "I found it.

    Code:
    SELECT 
    	SP.school_id [@school], 
    	SP.file_generation_time [@file_generation_time],
    	(SELECT survey.course_id as [@Course], 
    			survey.survey_date as [@SurveyDate], 
    			(SELECT SS.survey_question as [@QuestionCode], SS.survey_score as [@Score]
    			FROM dbo.survey2014_score as SS
    			WHERE SS.survey2014_id = survey.id
    			FOR XML PATH('Question'), TYPE
    			) 
    	FROM dbo.survey2014 as survey
    	WHERE SP.id= survey.survey_package_id
    	FOR XML PATH('Survey'), TYPE
    	)
    FROM dbo.survey_package AS SP
    FOR XML PATH('surveys2014')
    "


  3. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ignore me: missed the end of your script.
    George
    Home | Blog

  4. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I found it.

    Code:
    SELECT 
    	SP.school_id [@school], 
    	SP.file_generation_time [@file_generation_time],
    	(SELECT survey.course_id as [@Course], 
    			survey.survey_date as [@SurveyDate], 
    			(SELECT SS.survey_question as [@QuestionCode], SS.survey_score as [@Score]
    			FROM dbo.survey2014_score as SS
    			WHERE SS.survey2014_id = survey.id
    			FOR XML PATH('Question'), TYPE
    			) 
    	FROM dbo.survey2014 as survey
    	WHERE SP.id= survey.survey_package_id
    	FOR XML PATH('Survey'), TYPE
    	)
    FROM dbo.survey_package AS SP
    FOR XML PATH('surveys2014')
    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

Posting Permissions

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