Processing Xml Data in SQL Server

  • Article
  1. Processing Xml Data in SQL Server.

The below code demonstrates the use of xml data as input and converting xml data into temporary table.

 Declare @xmlData xml = '<Root><ParentElement GuidanceKitId="4ffbfee7-0878-4c67-9003-89e0b29b4684" LocaleId="920"></ParentElement><ParentElement GuidanceKitId="d65ddd7d-cb99-e111-96e5-00155d16e526" LocaleId="123"></ParentElement></Root>'
 DECLARE @handle INT;
  
 EXEC sp_xml_preparedocument @handle OUTPUT, @xmlData;
         SELECT 
                 cast(GuidanceKitId as uniqueidentifier) GuidanceKitId,                                                 
                 cast(LocaleId as int) LocaleId,
                 5 GuidanceKitRowId -- Constant Column
         INTO #TmpTable
         FROM 
         OPENXML (    @handle    ,'/Root/ParentElement'    ,1    )
         WITH 
         (
                 GuidanceKitId uniqueidentifier,                
                 LocaleId int
         )R
  
  
 select * from #TmpTable

 

Result:

image