XML Integration in SQL Server – SQL Server Tutorial

XML Integration in SQL Server :-

• Xml is simple and
• Unicode
• And platform independent.
• Transfer data from one file to another
• For each record one tag is created with name “row”
• Select * from emp for xml row
• Select * from emp For xml path.
•  select * from emp for xml   xpath root(‘emp’)

Transfering data from xml to sql server :-

Exec sp_preparedocument :-

• It is used to prepare xml document.

Exec sp_xml_removedocument  :-

• It is used to remove the document.

Example  :-

          Declare @x  int

          Declar @s  varchar (1000)

       Set @s =<root>

             <cust cid =”1” cname=”A”></cust>

              <cust cid =”z” cname=”B”></cust>

                     </root>

Exec   sp_xml_preparedocument  @x  output,@s

             Insert  into cust

        Select  * from  openxml(@x,’/root/cust’)

           With

                   (cid int,cname varchar(20)).