SQL server 2014 query update to insert XML node successful but not inserting node



I am trying to complete a homework assignment to create, insert, modify, and xquery a XML table. I've so far been able to create the table and insert some rows of data; however, I am stuck on the modified portion. I have the query created and it executes successfully, but it does not insert the new materials node. I have to insert this child node under all feature nodes.



CREATE TABLE Prod_Catalog
(
ID int NOT NULL Primary Key,
xCol xml NOT NULL,
)


-



INSERT INTO dbo.Prod_Catalog VALUES
(1,'<root><productdescription ProductID="P5500xs" ProductName="Brava 55 TV">
<features>Features
<warranty>5 years</warranty>
<maintenance>Dont hit with hammer</maintenance>
</features></productdescription>
</root>'),
(2,'<root><productdescription ProductID="G29-2" ProductName="Panasonic 1100Watt Microwave">
<features>Features
<warranty>1 year</warranty>
<maintenance>Dont cook metal forks!</maintenance>
</features></productdescription>
</root>'),
(3,'<root><productdescription ProductID="LG22-XL" ProductName="LG 20 cubic refrigerator">
<features>Features
<warranty>1 year</warranty>
<maintenance>Makes things cold.</maintenance>
</features></productdescription>
</root>')


-



UPDATE dbo.Prod_Catalog
SET xCol.modify('insert <materials>Electronics,Glass,Flashy Lights</materials> as first into (/root/productiondescription[@ProductID=("P5500xs")]/features)[1]');
GO


-



(3 row(s) affected)


-



<root><productdescription ProductID="P5500xs" ProductName="Brava 55 TV"><features>Features<warranty>5 years</warranty><maintenance>Dont hit with hammer</maintenance></features></productdescription></root>
<root><productdescription ProductID="G29-2" ProductName="Panasonic 1100Watt Microwave"><features>Features<warranty>1 year</warranty><maintenance>Dont cook metal forks!</maintenance></features></productdescription></root>
<root><productdescription ProductID="LG22-XL" ProductName="LG 20 cubic refrigerator"><features>Features<warranty>1 year</warranty><maintenance>Makes things cold.</maintenance></features></productdescription></root>


-


Any advise or direction to get this to truly work is much appreciated.


Thanks!


No comments:

Post a Comment