Re: From varchar(max) to xml

Tech-Archive recommends: Fix windows errors by optimizing your registry

psychodad71 via SQLMonster.com wrote:
I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
Column a contains the following data:

Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan

The first line contains the column header, the following the data.

The data should be transferred to column b with the following xml-structure:

<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>

The number of columns and the column names are various.

Any ideas?

If your database supports access to external scripting languages, dump
column a out and pass it through the following filter

awk -F\; 'BEGIN {ORS=""} {if(NR==1)n=split($0,gi);else{for(i=1;i<=NF;++i)print "<" gi[i] ">" $i "</" gi[i] ">";print "\n"}}'

and read the result into column b. The GNU awk processor for Windows can
be downloaded from http://gnuwin32.sourceforge.net/packages/gawk.htm

///Peter
.