Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pereayel
Starting Member
6 Posts |
 Posted - 2010-03-18 : 17:00:47
|
| Hi all,I have an xml file that contains keywords and categories for a given product. I am loading the keywords and categories to a sql table using the the updaate below. This only loads the first keyword and first category. My goal is to have it like this:id keyword category1 1 Key, Key2, Key3 cat1, cat2, cat2UPDATE whmMetadataSET [ID] = myXml.id ,keywords = myXml.keywords ,categories = myXml.categories FROM OPENXML(@idoc,'/products/product',1) WITH ( [ID] INT 'id' ,keywords NVARCHAR(500) 'keywords/keyword[position()<10]' ,categories NVARCHAR(24) 'categories/category[position()<10]') myXmlWHERE whmMetadata.[ID] = myXml.id My xml file is similar to this:<products> <product> <id>567</id> <keywords> <keyword>cross</keyword> <keyword>Easter</keyword> <keyword>ancient symbol</keyword> <keyword>Good Friday</keyword> <keyword>outdoor</keyword> <keyword>air</keyword> <keyword>sky</keyword> <keyword>view</keyword> <keyword>cloud</keyword> <keyword>clouds</keyword> </keywords> <categories> <category>category1</category> <category>category2</category> <category>category3</category> <category>category4</category> <category>category5</category> <category>category6</category> <category>category7</category> <category>category8</category> <category>category9</category> <category>category10</category> </categories> </product> </products> Thanks |
|
|
|
|
|
Upload keywords and categories from an xml file