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>
No comments:
Post a Comment