Insert XML File Data into Existing Table
1
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="izzychan@yahoo.com"/>
<Customer LastName="Prine" FirstName="John" Password=""
EmailAddress="johnprine@gmail.com"/>
<Customer LastName="Kitchen" FirstName="Kathy" Password=""
EmailAddress="kathykitchen@sbcglobal.net"/>
</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!
sql-server xml asked Nov 25 '18 at 1:47
RyanRyan
6027
add a comment |
1
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="izzychan@yahoo.com"/>
<Customer LastName="Prine" FirstName="John" Password=""
EmailAddress="johnprine@gmail.com"/>
<Customer LastName="Kitchen" FirstName="Kathy" Password=""
EmailAddress="kathykitchen@sbcglobal.net"/>
</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!
sql-server xml asked Nov 25 '18 at 1:47
RyanRyan
6027
add a comment |
1
1
1
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="izzychan@yahoo.com"/>
<Customer LastName="Prine" FirstName="John" Password=""
EmailAddress="johnprine@gmail.com"/>
<Customer LastName="Kitchen" FirstName="Kathy" Password=""
EmailAddress="kathykitchen@sbcglobal.net"/>
</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!
sql-server xml asked Nov 25 '18 at 1:47
RyanRyan
6027
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="izzychan@yahoo.com"/>
<Customer LastName="Prine" FirstName="John" Password=""
EmailAddress="johnprine@gmail.com"/>
<Customer LastName="Kitchen" FirstName="Kathy" Password=""
EmailAddress="kathykitchen@sbcglobal.net"/>
</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!
sql-server xml sql-server xml asked Nov 25 '18 at 1:47
RyanRyan
6027
asked Nov 25 '18 at 1:47
RyanRyan
6027
asked Nov 25 '18 at 1:47
RyanRyan
6027
asked Nov 25 '18 at 1:47
RyanRyan
6027
asked Nov 25 '18 at 1:47
RyanRyan
6027
6027
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
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
izzychan@yahoo.com Isabella Chan
johnprine@gmail.com John Prine
kathykitchen@sbcglobal.net Kathy Kitchen
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53463992%2finsert-xml-file-data-into-existing-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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
izzychan@yahoo.com Isabella Chan
johnprine@gmail.com John Prine
kathykitchen@sbcglobal.net Kathy Kitchen
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
add a comment |
2
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
izzychan@yahoo.com Isabella Chan
johnprine@gmail.com John Prine
kathykitchen@sbcglobal.net Kathy Kitchen
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
add a comment |
2
2
2
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
izzychan@yahoo.com Isabella Chan
johnprine@gmail.com John Prine
kathykitchen@sbcglobal.net Kathy Kitchen
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
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
izzychan@yahoo.com Isabella Chan
johnprine@gmail.com John Prine
kathykitchen@sbcglobal.net Kathy Kitchen
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
answered Nov 25 '18 at 2:18
John CappellettiJohn Cappelletti
46.6k62546
46.6k62546
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
add a comment |
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
Thank you so much that worked well for me! I could not for the life of me get that haha
– Ryan
Nov 25 '18 at 2:27
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
@Ryan Happy to help. We all get stuck from time to time :)
– John Cappelletti
Nov 25 '18 at 2:28
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53463992%2finsert-xml-file-data-into-existing-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
