Regarding xml export??
hai eugene,
here attaching code have given me according requirement. have 1 more requirement.
for table temptable_xref , appstable following xml query ok.
i have generated 2 xml files with other 2 tables
say temptable_xref1 , appstable.
and temptable_xref2 , appstable.
i have written query same following.
i got 3 xml files . according our requirement need these in 1 xml file only.
is there process generate such xml file single query?
with xmlnamespaces (default 'http://www.siebel.com/uan/siebelbias/sharedcomponents/commonobjects/cocommon')
select
'bus unit' as "idxref/@name",
'siebel_ucm' as "idxref/appinstance/@name",
(select
'cust_'+temptable_xref.ucm_uid as "@commonid",
temptable_xref.ucm_uid as "text()"
from temptable_xref, appstable where appstable.name='siebel_ucm'
for xml path('appid'), type) as "idxref/appinstance/*",
null as "idxref/dummyseparator1",
'siebel_80' as "idxref/appinstance/@name",
(select
'cust_'+ temptable_xref.ucm_uid as "@commonid",
temptable_xref.siebel_id as "text()"
from temptable_xref, appstable where appstable.name='siebel_80'
for xml path('appid'), type) as "idxref/appinstance/*",
null as "idxref/dummyseparator2",
'ixmal_01' as "idxref/appinstance/@name",
(select
'cust_'+ temptable_xref.ucm_uid as "@commonid",
temptable_xref.ixmal_id as "text()"
from temptable_xref, appstable where appstable.name='ixmal_01'
for xml path('appid'), type) as "idxref/appinstance/*"
for xml path('listofidxrefdata')
you can use same sql server 2005 features in original query – xml path , nesting xml using sub-query syntax. here’s example 2 tables temptable_xref , temptable_xref1:
with xmlnamespaces (default 'http://www.siebel.com/uan/siebelbias/sharedcomponents/commonobjects/cocommon')
select
'bus unit' as "idxref/@name",
'siebel_ucm' as "idxref/appinstance/@name",
(select
'cust_'+temptable_xref.ucm_uid as "@commonid",
temptable_xref.ucm_uid as "text()"
from temptable_xref, appstable where appstable.name='siebel_ucm'
for xml path('appid'), type) as "idxref/appinstance/*",
null as "idxref/dummyseparator1",
'siebel_80' as "idxref/appinstance/@name",
(select
'cust_'+ temptable_xref.ucm_uid as "@commonid",
temptable_xref.siebel_id as "text()"
from temptable_xref, appstable where appstable.name='siebel_80'
for xml path('appid'), type) as "idxref/appinstance/*",
null as "idxref/dummyseparator2",
'ixmal_01' as "idxref/appinstance/@name",
(select
'cust_'+ temptable_xref.ucm_uid as "@commonid",
temptable_xref.ixmal_id as "text()"
from temptable_xref, appstable where appstable.name='ixmal_01'
for xml path('appid'), type) as "idxref/appinstance/*"
null as "dummyseparator1",
'payment' as "idxref/@name",
'siebel_ucm' as "idxref/appinstance/@name",
(select
'pay-'+temptable_xref1.ucm_uid as "@commonid",
temptable_xref1.ucm_uid as "text()"
from temptable_xref1, appstable where appstable.name='siebel_ucm'
for xml path('appid'), type) as "idxref/appinstance/*",
null as "idxref/dummyseparator1",
'siebel_80' as "idxref/appinstance/@name",
(select
'cust_'+ temptable_xref1.ucm_uid as "@commonid",
temptable_xref1.siebel_id as "text()"
from temptable_xref, appstable where appstable.name='siebel_80'
for xml path('appid'), type) as "idxref/appinstance/*",
null as "idxref/dummyseparator2",
'ixmal_01' as "idxref/appinstance/@name",
(select
'cust_'+ temptable_xref1.ucm_uid as "@commonid",
temptable_xref1.ixmal_id as "text()"
from temptable_xref, appstable where appstable.name='ixmal_01'
for xml path('appid'), type) as "idxref/appinstance/*"
for xml path('listofidxrefdata')
best regards,
eugene kogan,
technical lead,
sql server engine
---------------------------
this posting provided "as is" no warranties, , confers no rights.
the code samples not verified , may contain syntax errors.
SQL Server > SQL Server XML
Comments
Post a Comment