CODEWITHSUNDEEP
sql-serverxml
Ryan
Ryan

Reputation: 199

Insert XML File Data into Existing Table

I'm trying to insert contents of an XML file into an existing table. So far I only have the below:

DECLARE @NewCustomers XML;
SET @NewCustomers = '
<NewCustomers>

<Customer LastName="Chan" FirstName="Isabella" Password="" 
EmailAddress="[email protected]"/>

<Customer LastName="Prine" FirstName="John" Password="" 
EmailAddress="[email protected]"/>

<Customer LastName="Kitchen" FirstName="Kathy" Password="" 
EmailAddress="[email protected]"/>

</NewCustomers>
'
;

INSERT INTO Customers (EmailAddress, Password, FirstName, LastName)
Values(
@NewCustomers
)
GO

But I get an error "There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

And Completely stumped here anything can help Thank you!

Upvotes: 1

Views: 183

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

The XML will not parse itself.

Example

INSERT INTO Customers (EmailAddress, Password, FirstName, LastName)
Select EMailAddress = x.v.value('@EmailAddress','VARCHAR(150)')
      ,Password     = x.v.value('@Password','VARCHAR(150)')
      ,FirstName    = x.v.value('@FirstName','VARCHAR(150)')
      ,LastName     = x.v.value('@LastName','VARCHAR(150)')
 From  @NewCustomers.nodes('NewCustomers/Customer') x(v)

The Select will produce the following

EMailAddress                    Password    FirstName   LastName
[email protected]                          Isabella    Chan
[email protected]                         John        Prine
[email protected]                  Kathy       Kitchen

Upvotes: 2

Related Questions