0

I have user table where it held the user information. 

Create table #Users (
userid uniqueidentifier, 
NationalID int,
Firstname varchar (25), 
LastName varchar(25), 
suffix varchar(25), 
DOB datetime, 
Gender nvarchar(2),
position nvarchar(200)

 ) 

  INSERT INTO #USERS
  Values 
  ('8F6390AF-8C7C-4036-AEEC-7152161A23A7',
  1234567, 
  'Bob',
 'gates',
  'II',
  '01-01-1987', 
  'M',
  'Manager')

I want to return the result in XML using For XML Raw and I have a default name space (https://Somthing) that I want on the root node using below sub query

  WITH xmlnamespaces(Default 'https://Somthing')
  SELECT
  (
    SELECT GetDate() as AsOfDate, NationalID,
        (
        SELECT FirstName, 
                LastName, 
                Suffix
        FROM #Users AS [Name]
        WHERE [Name].UserID= u.UserID
        FOR XML RAW('Name'), TYPE,ELEMENTS
    ),
DOB, 
Gender
FROM #Users AS EmployeeInfo
 WHERE EmployeeInfo.UserID = u.UserID
        FOR XML Raw ('EmployeeInfo'), TYPE, ELEMENTS)
       From #Users u
       WHERE u.userid = '8F6390AF-8C7C-4036-AEEC-7152161A23A7'
      FOR XML Raw ('Transcript'),Type, ELEMENTS XSINIL

The result I get is redundant name space in all nodes. How should I remove these redundant in other nodes and keep it only on top 

            <Transcript xmlns:xsi="http://www.w3.org/2001/XMLSchema- 
             instance" xmlns="https://Somthing">
          <EmployeeInfo xmlns="https://Somthing">
            <AsOfDate>2018-12-13T17:54:11.183</AsOfDate>
            <NationalID>1234567</NationalID>
            <Name xmlns="https://Somthing">
              <FirstName>Bob</FirstName>
              <LastName>gates</LastName>
              <Suffix>II</Suffix>
            </Name>
            <DOB>1987-01-01T00:00:00</DOB>
            <Gender>M</Gender>
          </EmployeeInfo>
        </Transcript>

I want my result to be something like below 

   <Transcript xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="https://Somthing">
          <EmployeeInfo>
            <AsOfDate>2018-12-13T17:54:11.183</AsOfDate>
            <NationalID>1234567</NationalID>
            <Name>
              <FirstName>Bob</FirstName>
              <LastName>gates</LastName>
              <Suffix>II</Suffix>
            </Name>
            <DOB>1987-01-01T00:00:00</DOB>
            <Gender>M</Gender>
          </EmployeeInfo>
        </Transcript>

    Thanks
Shawn
  • 45
  • 1
  • 9
  • Check this https://stackoverflow.com/questions/39057036/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-in-sql-fu and this too.. https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ – CR241 Dec 14 '18 at 00:52
  • Thanks, so far all of these solutions were for xml path. I couldnt find anything for XML RAW or XML Auto. – Shawn Dec 14 '18 at 14:52

0 Answers0