locked
The FOR XML CLAUSE in SQL Server 2019 is not respecting WITH XMLNAMESPACES RRS feed

  • Question

  • The SQL below is producing different results in SQL Server 2017 and SQL Server 2019 with SQL Server 2019 not returning the expected XML namespace. SQL Server 2017 was tested using Standard edition on Windows, SQL Server 2019 was tested using Standard edition on Linux. Is this a known issue? Thanks.

    DECLARE @PERSON TABLE (LASTNAME NVARCHAR(50), DOB DATETIME2);

    INSERT INTO @PERSON(LASTNAME, DOB)
    VALUES (N'BEDI', N'1970-01-01'), (N'PATEL', N'1980-11-11');

    WITH XMLNAMESPACES(DEFAULT 'http://www.people.com/names')
    SELECT LASTNAME, DOB FROM @PERSON
    FOR XML PATH('PERSON'), ROOT('PEOPLE'), ELEMENTS;

    SQLServer 2017:

    <PEOPLE xmlns="http://www.people.com/names">
      <PERSON>
        <LASTNAME>BEDI</LASTNAME>
        <DOB>1970-01-01T00:00:00</DOB>
      </PERSON>
      <PERSON>
        <LASTNAME>PATEL</LASTNAME>
        <DOB>1980-11-11T00:00:00</DOB>
      </PERSON>
    </PEOPLE>

    SQLServer2019:

    <PEOPLE xmlns="">
      <PERSON>
        <LASTNAME>BEDI</LASTNAME>
        <DOB>1970-01-01T00:00:00</DOB>
      </PERSON>
      <PERSON>
        <LASTNAME>PATEL</LASTNAME>
        <DOB>1980-11-11T00:00:00</DOB>
      </PERSON>
    </PEOPLE>


    Thursday, August 6, 2020 3:39 PM

Answers

  • Hi Paul,

    I slightly modified your T-SQL.

    It is correctly producing XML with the namespace for both SQL Server 2017 on Windows and 2019 on Linux.

    You can try it here: 

    SQL Fiddle

    DECLARE @PERSON TABLE (LASTNAME NVARCHAR(50), DOB DATETIME2);
    
    INSERT INTO @PERSON(LASTNAME, DOB)
    VALUES 
    (N'BEDI', N'1970-01-01'), 
    (N'PATEL', N'1980-11-11');
    
    ;WITH XMLNAMESPACES(DEFAULT 'http://www.people.com/names')
    SELECT LASTNAME, DOB 
    FROM @PERSON
    FOR XML PATH('PERSON'), TYPE, ROOT('PEOPLE');
    Thursday, August 6, 2020 3:56 PM

All replies

  • I executed your script on SQL server 2019 developer version on the Windows and got the same output as the one you posted for SQL server 2017.

    A Fan of SSIS, SSRS and SSAS




    Thursday, August 6, 2020 3:53 PM
  • Hi Paul,

    I slightly modified your T-SQL.

    It is correctly producing XML with the namespace for both SQL Server 2017 on Windows and 2019 on Linux.

    You can try it here: 

    SQL Fiddle

    DECLARE @PERSON TABLE (LASTNAME NVARCHAR(50), DOB DATETIME2);
    
    INSERT INTO @PERSON(LASTNAME, DOB)
    VALUES 
    (N'BEDI', N'1970-01-01'), 
    (N'PATEL', N'1980-11-11');
    
    ;WITH XMLNAMESPACES(DEFAULT 'http://www.people.com/names')
    SELECT LASTNAME, DOB 
    FROM @PERSON
    FOR XML PATH('PERSON'), TYPE, ROOT('PEOPLE');
    Thursday, August 6, 2020 3:56 PM
  • Hi Yitzhak,

    I see. I was missing the TYPE directive.

    Thanks for your help. I have marked as answer.

    Thursday, August 6, 2020 4:37 PM
  • Hi Paul,

    Glad to hear that the proposed solution is working for you.

    P.S. Please connect with me on LinkedIn.

    Thursday, August 6, 2020 4:42 PM