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