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 

About the Author

Barry S. StahlBarry S. Stahl (he/him/his) - Barry is a .NET Software Engineer who has been creating business solutions for enterprise customers since the mid 1980s. Barry is also an Election Integrity Activist, baseball and hockey fan, husband of one genius and father of another, and a 40 year resident of Phoenix Arizona USA. When Barry is not traveling around the world to speak at Conferences, Code Camps and User Groups or to participate in GiveCamp events, he spends his days as a Solution Architect for Carvana in Tempe AZ and his nights thinking about the next AZGiveCamp event where software creators come together to build websites and apps for some great non-profit organizations.

For more information about Barry, see his About Me Page.

Barry has started delivering in-person talks again now that numerous mechanisms for protecting our communities from Covid-19 are available. He will, of course, still entertain opportunities to speak online. Please contact him if you would like him to deliver one of his talks at your event, either online or in-person. Refer to his Community Speaker page for available options.

Social Media

Tag Cloud