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

    Answered: SSIS doubles XML root element

    Hi,

    I create XML's using SSIS. I use the method described here. Normally it works fine, but with a new - and very simple - XML, I have a strange and stubborn problem.

    The expected XML is this:
    Code:
    <Exams Centre="MyGreatSchool" Runtime="2016-04-13T15:35:40.170">
      <Exam ExamYear="2016" CourseReference="123" ExamMoment="2016-05-05T09:00:00" />
      <Exam ExamYear="2016" CourseReference="456" ExamMoment="2016-06-06T13:00:00" />
    </Exams>
    But SSIS generates this:
    Code:
    <Exams Centre="MyGreatSchool" Runtime="2016-04-13T15:35:40.170">
      <Exam ExamYear="2016" CourseReference="123" ExamMoment="2016-05-05T09:00:00" />
      <Exam ExamYear="2016" CourseReference="456" ExamMoment="2016-06-06T13:00:00" />
    </Exams>,
    <Exams Centre="MyGreatSchool" Runtime="2016-04-13T15:35:40.170">
      <Exam ExamYear="2016" CourseReference="123" ExamMoment="2016-05-05T09:00:00" />
      <Exam ExamYear="2016" CourseReference="456" ExamMoment="2016-06-06T13:00:00" />
    </Exams>
    
    The root element Exams occurs twice in the result, separated by a comma from its first occurrence.

    When I run the XML-generating code interactively in SSMS, I get the expected result, but when SSIS generates it, it produces the root element twice. The result is not well formed XML and can't be processed.

    Here are the DDL en DML scripts used to get the above result (simplified code):
    Code:
    CREATE TABLE work.bulkexport_header(
    	centre_id			INT				NOT NULL,
    	Centre				NVARCHAR(255)	NOT NULL,
    	Runtime				DATETIME		NOT NULL	DEFAULT(GetDate())
    );
    
    CREATE TABLE work.bulkexport_Body(
    	centre_id			int					NOT NULL,
    	ExamYear			int					NOT NULL,
    	CourseReference		int					NOT NULL,
    	ExamMoment			datetime2			NOT NULL
    )
    
    INSERT INTO work.bulkexport_header (centre_id, Centre, Runtime) VALUES(1, 'MyGreatSchool', getdate());
    INSERT INTO work.bulkexport_Body(centre_id, ExamYear, CourseReference, ExamMoment) VALUES
    (1, 2016, 123, '2016-05-05 09:00'), (1, 2016, 456, '2016-06-06 13:00')
    The XML is generated with this script:
    Code:
    SELECT (
    SELECT   
    	HEADER.Centre [@Centre],
    	HEADER.Runtime [@Runtime],
    	(
    	SELECT  
    		ExamYear [@ExamYear]
    		,CourseReference [@CourseReference]
    		,ExamMoment [@ExamMoment]
    	FROM work.bulkexport_Body as BODY
    	WHERE HEADER.centre_id = BODY.centre_id
    	ORDER BY ExamYear, ExamMoment
    	FOR XML PATH('Exam'), TYPE
    	)
    FROM work.bulkexport_header as HEADER
    FOR XML PATH('Exams')
    ) as COL_XML
    To get nicely formatted XML, you can omit the red SELECT ( and ) as COL_XML from the above code. If you execute the above code, I bet you get the expected result.

    In the past I have had to solve the problem that SSIS sometimes inserts two BOM's (Byte Order Mark) in de generated XML files. But it's the first time it am confronted with this problem.

    I've been struggling for hours with this problem. I hope someone on the forum can help me.
    Last edited by Wim; 04-13-16 at 11:34.
    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 MCrowley

    "I suspect the SQL step is giving the same result each time, regardless of what tool you are using. I would focus on the flat file definition. I suspect the comma is a delimiter (probably row but maybe a column delimiter)."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I suspect the SQL step is giving the same result each time, regardless of what tool you are using. I would focus on the flat file definition. I suspect the comma is a delimiter (probably row but maybe a column delimiter).

  4. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hey MCrowley,

    I will throw the flat file away and restart from scratch. Maybe that will help.

    Thank you.
    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

  5. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I restarted from scratch and it solved the problem.
    Yet very, very little code is involved so I still wonder what caused the problem.

    Thank you for pointing me in the right direction.
    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
  •