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>
  <CustomerID>1</CustomerID>
  <Fname>William</Fname>
  <Lname>Mendoza</Lname>
  <DOB>1/1/1980</DOB>
  <BuenaVistaID>C000001</BuenaVistaID>
  <DocumentType>DNI</DocumentType>
  <DocumentNumber>12356987</DocumentNumber>
  <State>FL</State>
  <Country>US</Country>
  <Mobile>9542135689</Mobile>
  <Email>williameduardo@hotmail.com</Email>
  <Ocupation>Engineer</Ocupation>
  <MaritialStatus>Single</MaritialStatus>
</row>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

-- student's data first

SELECT * 
  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 
OpenXML

Related Posts

No Comments Yet.

Leave a reply

You must be logged in to post a comment.