Share via


Processing Xml Data in SQL Server

  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