How to convert an XML file to a data table

This sample transform an xml type into a SQL dataset result

DECLARE @docHandle int

DECLARE @xmlDocument XML
SET @xmlDocument = N'<row>

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

-- student's data first

  FROM OPENXML(@docHandle, N'/row',2) 
    WITH ([CustomerID] [int] ,
 [Fname] [varchar](50)  ,
 [Lname] [varchar](50)  ,
 [DOB] [datetime]  ,
 [UserID] [uniqueidentifier] ,
 [BuenaVistaID] [varchar](50) ,
 [DocumentType] [varchar](50) ,
 [DocumentNumber] [varchar](50) ,
 [Address1] [varchar](200) ,
 [Address2] [varchar](200) ,
 [City] [varchar](50) ,
 [State] [varchar](50) ,
 [ZipCode] [varchar](50) ,
 [Country] [varchar](50) ,
 [Mobile] [varchar](50) ,
 [HomePhone] [varchar](50) ,
 [OtherPhone] [varchar](50) ,
 [Email] [varchar](50) ,
 [Ocupation] [varchar](50) ,
 [Company] [varchar](50) ,
 [MaritialStatus] [varchar](50) ,
 [AnualIncome] [decimal](18, 4) ,
 [Fax] [varchar](50) ,
 [ExtraProperty] [xml] )

EXEC sp_xml_removedocument @docHandle 

Related Posts

No Comments Yet.

Leave a reply

You must be logged in to post a comment.