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 


Comments (0)

Skip to main content