Use XML to break down strings in MSSQL

Today, in MSSQLTo process a string of email addresses. Separated by semicolons. I used to write a split function myself. This time I want to use XML for processing.

MSSQL2000 and MsSQL2005DatabaseXML support is somewhat different. At least MsSQL2005 has more features.

 

CodeI:
This code is available in MSSQL2005 test successful, in MSSQL2000 failed. Finally, a table variable is generated. You can convert a table variable to the required data.

-- Define a string for testing declare @ CC varchar (1000) set @ cc = 'Hello @ 163.com; world@hotmail.com; iloveyou@yahoo.com '-- Define a table variable declare @ emailtable table (email varchar (50 )) -- Define an XML variable declare @ XML xmlset @ xml = cast ('<email>' + Replace (@ CC, ';', '</Email> <email> ') + '</Email>' as XML) insert into @ emailtable (email) Select T. i. value ('. ', 'varchar (50)') from @ XML. nodes ('email ') T (I) Select * From @ emailtable
 

Code 2:
This code is available in MSSQL2005 test successful, in MSSQL2000 No time test. Modify the settings next week. Finally, a table variable is generated. You can convert a table variable to the required data.

 -- Define a string for testing declare @ CC varchar (1000) set @ cc = 'Hello @ 163.com; world@hotmail.com; iloveyou@yahoo.com '-- Define a table variable declare @ emailtable table (email varchar (50) -- XML string variable declare @ xml2 varchar (1000) -- XML handlerdeclare @ xmlid int/* pay attention to this. I don't know why. The 
   
   
     email1 
    
   
     email2 
    
   
     format can only process one email address. 
     
      
      
        email1 
        
      
      
        email2 
        
     
       can process all emails. */Set @ xml2 = '
       
        
        
         ' + Replace (@ CC ,';', '
          
        
        
         ') + '
          
      ' exec sp_xml_preparedocument @ xmlid output, @ xml2insert into @ emailtable select * From openxml (@ xmlid, '/root/data', 2) with (email varchar (50) 'address ') select * From @ emailtablesp_xml_removedocument @ xmlid 
     
    
   
  

The XML method is much better than the one I wrote before. If MsSQL2000 can pass. You need to modify the code.

Publish via wiz