Sep 20, 2011

How to use XML To Save the data in Store Procedure in MS SQL 2005/2008


DECLARE @XMLDOC VARCHAR(MAX)
DECLARE @xml_hndl INT



SET @XMLDOC='<?xml version="1.0" encoding="ISO-8859-1"?>

<SBU><row><SBU_code>QQQ</SBU_code><SbuOrder></SbuOrder></row><row><SBU_code>WW</SBU_code><SbuOrder></SbuOrder></row><row><SBU_code>zz</SBU_code><SbuOrder></SbuOrder></row><row><SBU_code>PS</SBU_code><SbuOrder>1</SbuOrder></row><row><SBU_code>IS</SBU_code><SbuOrder>2</SbuOrder></row><row><SBU_code>CP</SBU_code><SbuOrder>3</SbuOrder></row><row><SBU_code>DG</SBU_code><SbuOrder>4</SbuOrder></row><row><SBU_code>RG</SBU_code><SbuOrder>5</SbuOrder></row><row><SBU_code>CT</SBU_code><SbuOrder>6</SbuOrder></row><row><SBU_code>test</SBU_code><SbuOrder>7</SbuOrder></row><row><SBU_code>CC</SBU_code><SbuOrder>8</SbuOrder></row><row><SBU_code>PW</SBU_code><SbuOrder>9</SbuOrder></row><row><SBU_code>RR</SBU_code><SbuOrder>10</SbuOrder></row></SBU>'



EXEC Sp_xml_preparedocument  @xml_hndl OUTPUT,  @XMLDOC


--Below Read the whole data and Insert it into table --


SELECT * FROM   OPENXML(@xml_hndl, '/SBU/row', 1)
          
WITH ( sbu_code VARCHAR(MAX) 'SBU_code',
                 
sbuorder VARCHAR(MAX) 'SbuOrder' )


--Remove document ----
EXEC sp_xml_removedocument @xml_hndl                  

No comments:

Post a Comment

What is the use of n-tier architecture and 3-tier architecture?

how to implement 3-tier architecture in asp.net using c#. 3-Tier architecture is also called layered architecture. Some people called it ...