Upload keywords and categories from an xml file to sql server

[Pereayel]

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 category

1 Key, Key2, Key3 cat1, cat2, cat2



UPDATE whmMetadata

SET [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]'
)

myXml

WHERE 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

 

[kristof]

Hi,

Check this link about the 'XML Bulk Load component':

http://support.microsoft.com/kb/316005

Should be pretty easy considering the low complexity of your XML file.

 

[Pereayel]

Thanks for your reply kristof. Does the bulk import work with an xml from an http feed?

 

[NastiaMurzin]

Basically, u need to create a keyword table.

use tableschema;
go
create procedure Table2
as
if table Table2 is not null
drop table Table2;
go
create table Table2
(id int as primary key not null auto_increment,
keyword varchar(50) null,
category varchar(50) null);
go
insert into Table2 values('keyword', 'category');
go
insert into Table2 values('cross', 'category1');
go
insert into Table2 values('easter', 'category2');
go
insert into Table2 values('ancient symbol', 'category3');
go
insert into Table2 values('good friday', 'category4');
go
insert into Table2 values('outdoor', 'category5');
go
insert into Table2 values('air', 'category6');
go
insert into Table2 values('sky', 'category7');
go
insert into Table2 values('view', 'category8');
go
insert into Table2 values('cloud', 'category9');
go
insert into Table2 values('clouds', 'category10');
go
select * from Table2;
go