function getkeyvalue(s_keyname,s_keystr)
s_keybegin="<"+s_keyname+">"
s_keyend="</"+s_keyname+">"
s_i_begin=instr(s_keystr,s_keybegin)
s_i_end=instr(s_keystr,s_keyend)
if s_i_end<=s_i_begin+len(s_keybegin) then
getkeyvalue=""
exit function
else
getkeyvalue=mid(s_keystr,s_i_begin+len(s_keybegin),s_i_end-s_i_begin-len(s_keybegin))
exit function
end if
end function
function setkeyvalue(s_keyname,s_keystr,s_keyvalue)
if isnull(s_keyvalue) then
setkeyvalue=s_keystr
exit function
end if
s_keybegin="<"+s_keyname+">"
s_keyend="</"+s_keyname+">"
s_i_begin=instr(s_keystr,s_keybegin)
s_i_end=instr(s_keystr,s_keyend)
if s_i_end<=s_i_begin+len(s_keybegin) then
setkeyvalue=s_keystr+s_keybegin+s_keyvalue+s_keyend
exit function
else
setkeyvalue=mid(s_keystr,1,s_i_begin-1)+s_keybegin+s_keyvalue+s_keyend+mid(s_keystr,s_i_end+len(s_keyend))
exit function
end if
end function
If XML data in the table is less than 32K for each record, then you can directly unload the data as char. If XML data exceeds 32K for some records, then you have to unload the common data and the XML data separately. First, create a template for unloading XML into a PDS: TEMPLATE LOBFRV DSN 'AAA. ......
DECLARE @x xml
SET @x='
<root>
<ShopAccount>
<ActivityType>IA - PM Standing WO (for LPI report)</ActivityType>
<ProjectNo>R</ProjectNo>
</ShopAccount>
<ShopAccount>
......
