SQL Server XML - Example for adding Namespaces

Recently, my colleague came to me and said he was able to generate XML from relational data.
But he wasn't able to prefix some text before each XML tag.

After seeing his required XML format to be generated, I told him adding namespace will do.

Below is an example for adding multiple namespaces to the XML constructed by the FOR XML query:

--Sample data

DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subject VARCHAR(20),Marks INT)
INSERT @StudentInfo SELECT 1,'Sathya','Maths','60'
INSERT @StudentInfo SELECT 2,'Deepak','Maths','80'
--SELECT * FROM @StudentInfo









--Relational data - > XML using FOR XML clause
SELECT 
       Name    as 'tem/StudentName'
      ,Subject as 'tem/Subject'
      ,Marks   as 'tem/Marks'   
FROM @StudentInfo
FOR XML PATH ('Header'),root('Envelope')





<Envelope>
  <Header>
    <tem>
      <StudentName>Sathya</StudentName>
      <Subject>Maths</Subject>
      <Marks>60</Marks>
    </tem>
  </Header>
  <Header>
    <tem>
      <StudentName>Deepak</StudentName>
      <Subject>Maths</Subject>
      <Marks>80</Marks>
    </tem>
  </Header>
</Envelope>



 --Declaring namespaces that needs to be prefixed with each XML tag

;WITH XMLNAMESPACES ('http://tempuri.org/' as tem
                    ,'http://schemas.xmlsoap.org/soap/envelope/' as soapenv)
SELECT 
       Name    as 'tem:Stud/tem:StudentName'
      ,Subject as 'tem:Stud/tem:Subject'
      ,Marks   as 'tem:Stud/tem:Marks'   
FROM @StudentInfo
FOR XML PATH ('soapenv:Header'),root('soapenv:Envelope')



This is how the generated XML will look like after adding namespace:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
  <soapenv:Header>
    <tem:Stud>
      <tem:StudentName>Sathya</tem:StudentName>
      <tem:Subject>Maths</tem:Subject>
      <tem:Marks>60</tem:Marks>
    </tem:Stud>
  </soapenv:Header>
  <soapenv:Header>
    <tem:Stud>
      <tem:StudentName>Deepak</tem:StudentName>
      <tem:Subject>Maths</tem:Subject>
      <tem:Marks>80</tem:Marks>
    </tem:Stud>
  </soapenv:Header>
</soapenv:Envelope>

See Also




No comments: