XSolvo
Current user:   guest    Change     Preferences 
   List directory   History   Similar   Print version 
XSolvo 
   About XSolvo 
Articles 
html2xml 
tradufix 
TVProgramGrabber 
download 
Philosophy 
gallery 

Articles > Reading, processing XML data in SQL Server

 
rw-rw-r--   stga   wheel

Reading, processing XML data in SQL Server

A sample of how you can manupulate data in T-SQL that is returned from a procedure as xml data.

if object_id('tempdb..#tmp') is not null drop table #tmp

create table #tmp(xmltext text)

exec master..xp_cmdshell
'bcp "exec SP_GetDataAsXml 164406, 1561" queryout C:\tempfile.xml -T -S.\Stegar -n',
NO_OUTPUT

BULK INSERT #tmp
FROM 'C:\tempfile.xml'
WITH     ( DATAFILETYPE = 'widenative')

exec master..xp_cmdshell
'bcp "exec SP_GetMoreDataAsXml 164406, 1561" queryout C:\tempfile.xml -T -S.\Stegar -n',
NO_OUTPUT

BULK INSERT #tmp
FROM 'C:\tempfile.xml'
WITH     ( DATAFILETYPE = 'widenative')

declare @xmltext varchar(8000)

set @xmltext='<?xml version="1.0" encoding="ISO-8859-1"?>
<Tag>'
select @xmltext = @xmltext + convert(varchar(8000),xmltext) from #tmp
set @xmltext=@xmltext+'</Tag>'
print @xmltext

select @xmltext = replace(@xmltext,'SOMEDATA_','DATA_')

print @xmltext

exec SP_SaveXmlData @xmltext, 164406


Reference Reading, processing XML data in SQL Server
http://www.xsolvo.com/Articles/ReadingProcessingXMLDataInSQLServer


Prev. LOCALSERVER   query sp_who2 Next