Einfügen aus XML-file

Ich versuche, eine Kundentabelle mit einer XML-file zu aktualisieren, aber es zieht nicht die Werte aus dem XML in die variables, um in die Tabelle zu gelangen. Der Fehler, den ich bekomme "Kann den Wert NULL nicht in die Spalte 'EmailAddress' 'insert, obwohl es eindeutig einen Wert für EmailAddress gibt. Ich fühle mich wie ich vermisse etwas sehr einfaches, aber kann es nicht herausfinden. Irgendwelche Gedanken?

USE MyGuitarShop DECLARE @CustomerUpdate XML SET @CustomerUpdate = '<NewCustomers> <Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" /> <Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" /> <Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" /> </NewCustomers>'; INSERT Customers (EmailAddress, Password, FirstName, LastName) VALUES ( @CustomerUpdate.value('(/NewCustomers/Customer/EmailAddress)[1]', 'varchar(255)'), @CustomerUpdate.value('(/NewCustomers/Customer/Password)[1]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/FirstName)[1]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/LastName)[1]', 'varchar(60)') ); SELECT * FROM Customers 

Ich fühle mich wie mir etwas ganz einfaches fehlt …

Ja, du hast recht :-D, du vermisst das @ , um den Attribut- Wert zu lesen:

 DECLARE @CustomerUpdate XML SET @CustomerUpdate = '<NewCustomers> <Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" /> <Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" /> <Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" /> </NewCustomers>'; SELECT @CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[1]', 'varchar(255)'), @CustomerUpdate.value('(/NewCustomers/Customer/@Password)[1]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[1]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[1]', 'varchar(60)') 

UPDATE: Alle <Customer>

Versuche dies:

 DECLARE @Customers TABLE(EMailAddress VARCHAR(100),[Password] VARCHAR(100),FirstName VARCHAR(100),LastName VARCHAR(100)); DECLARE @CustomerUpdate XML SET @CustomerUpdate = '<NewCustomers> <Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" /> <Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" /> <Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" /> </NewCustomers>'; INSERT INTO @Customers (EmailAddress, Password, FirstName, LastName) SELECT c.value('@EmailAddress', 'varchar(255)') ,c.value('@Password', 'varchar(60)') ,c.value('@FirstName', 'varchar(60)') ,c.value('@LastName', 'varchar(60)') FROM @CustomerUpdate.nodes(N'/NewCustomers/Customer') AS A(c) SELECT * FROM @Customers 

Dieser Code funktioniert jetzt perfekt.

  USE MyGuitarShop DECLARE @CustomerUpdate XML SET @CustomerUpdate = '<NewCustomers> <Customer EmailAddress="izzychan@yahoo.com" Password="" FirstName="Isabella" LastName="Chan" /> <Customer EmailAddress="johnprine@gmail.com" Password="" FirstName="John" LastName="Prine" /> <Customer EmailAddress="kathykitchen@sbcglobal.net" Password="" FirstName="Kathy" LastName="Kitchen" /> </NewCustomers>'; INSERT Customers (EmailAddress, Password, FirstName, LastName) VALUES ( @CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[1]', 'varchar(255)'), @CustomerUpdate.value('(/NewCustomers/Customer/@Password)[1]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[1]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[1]', 'varchar(60)') ); INSERT Customers (EmailAddress, Password, FirstName, LastName) VALUES ( @CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[2]', 'varchar(255)'), @CustomerUpdate.value('(/NewCustomers/Customer/@Password)[2]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[2]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[2]', 'varchar(60)') ); INSERT Customers (EmailAddress, Password, FirstName, LastName) VALUES ( @CustomerUpdate.value('(/NewCustomers/Customer/@EmailAddress)[3]', 'varchar(255)'), @CustomerUpdate.value('(/NewCustomers/Customer/@Password)[3]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@FirstName)[3]', 'varchar(60)'), @CustomerUpdate.value('(/NewCustomers/Customer/@LastName)[3]', 'varchar(60)') ); SELECT * FROM Customers