Fetching data from xml content in SQL Server

Fetching data from xml content in SQL Server

DECLARE @ADDRESS_XML_STRING NVARCHAR(4000)   
DECLARE @IDOC INT    
Set @ADDRESS_XML_STRING= '<ADDRESS_INFORMATION><ADDRESS ADDRESS_TYPE="Current" ADDRESS_EFFECTIVE_START_DATE="2010-11-22" STREET1="7 Cherokee Rd" REGION1="Middlesex" REGION2="MA" CITY="Acton" ZIPCODE="01720" COUNTRY="US"></ADDRESS></ADDRESS_INFORMATION>'

EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @ADDRESS_XML_STRING  

SELECT 
          STREET1
          ,STREET2
          ,STREET3
          ,REGION1
          ,CITY
          ,STATE
          ,COUNTRY
          ,ZIPCODE
          ,ADDRESS_TYPE    
      INTO #TEMP_ADDRESS    
      FROM  
       (    
            SELECT        
               STREET1
               ,STREET2
               ,STREET3
               ,REGION1
               ,CITY
               ,STATE
               ,ZIPCODE
               ,ADDRESS_TYPE    
            FROM     
              OPENXML (@IDOC, '/ADDRESS_INFORMATION/ADDRESS', 1)     
                    WITH
                       (    
                          STREET1 VARCHAR(240)
                          ,STREET2 VARCHAR(240)
                           ,STREET3 VARCHAR(240)
                           ,REGION1 Varchar(240)
                           ,CITY VARCHAR(150)
                           ,STATE VARCHAR(150)
                           ,COUNTRY VARCHAR(60)
                           ,ZIPCODE VARCHAR(10)
                           ,ADDRESS_TYPE VARCHAR(50)
                         )    
                           WHERE STREET1 IS NOT NULL
                    ) q  
                    EXEC SP_XML_REMOVEDOCUMENT @IDOC

Select * FROM #TEMP_ADDRESS 

The Result will be :

Continue Reading →