The Application Development Experiences of an Enterprise Engineer

Sample SQL 2000 XML Query Courtesy of AE

Posted by bsstahl on 2006-05-25 and Filed Under: development 


Here are some Sample SQL 2000 XML Queries.

DECLARE @sxml varchar(8000)  
 SET @sxml = '<?xml version="1.0" encoding="UTF-8"?>  
 <Change xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="1.0">  
 <SourceApplication><Name>Intranet</Name></SourceApplication>  
 <DestinationApplication><Name>FundsDB</Name></DestinationApplication>  
 <UserName>JDoe</UserName><IPAddress>192.168.101.2</IPAddress>  
 <BusinessEntity><Name>Fund</Name></BusinessEntity><ChangeItems>  
 <ChangeItem><OriginalData>ABC Fund</OriginalData><NewData>123 Fund</NewData>  
 <DataID>5</DataID><Field><Name>Name</Name></Field></ChangeItem>  
 <ChangeItem><OriginalData>Type A</OriginalData><NewData>Type B</NewData>  
 <DataID>5</DataID><Field><Name>Type</Name></Field></ChangeItem>  
 </ChangeItems><ChangeDate>2004-10-12T12:57:00-07:00</ChangeDate>  
 <CommandText>s\_SaveFund</CommandText></Change>'  
  
 --- ----- -----   
  
 DECLARE @ixml int  
  
 --- ----- -----   
  
 EXEC dbo.sp\_xml\_preparedocument @ixml OUTPUT, @sxml  
  
 --- ----- -----   
  
 SELECT x.\*  
 FROM OPENXML( @ixml, '/Change', 1)  
 WITH ( SourceApplication varchar(64) 'SourceApplication/Name',  
 DestinationApplication varchar(64) 'DestinationApplication/Name',  
 UserName varchar(64) 'UserName',  
 IPAddress varchar(23) 'IPAddress',   
 BusinessEntity varchar(64) 'BusinessEntity/Name',  
 ChangeDate varchar(16) 'ChangeDate',  
 CommandText varchar(128) 'CommandText' ) x  
  
 --- ----- -----   
  
 SELECT x.\*  
 FROM OPENXML( @ixml, '/Change/ChangeItems/ChangeItem', 1)  
 WITH ( OriginalData varchar(2000) 'OriginalData',  
 NewData varchar(2000) 'NewData',  
 DataID varchar(32) 'DataID',  
 Field sysname 'Field/Name' ) x  
  
 --- ----- -----   
  
 EXEC dbo.sp\_xml\_removedocument @ixml  
  
 --- ----- -----
Tags: xml sql code sample