The Application Development Experiences of an Enterprise Developer

Sample SQL 2000 XML Query Courtesy of AE

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

DECLARE @sxml varchar(8000)
SET @sxml = '<?xml version="1.0" encoding="UTF-8"?>
<Change xmlns:xsi="" Version="1.0">
<ChangeItem><OriginalData>ABC Fund</OriginalData><NewData>123 Fund</NewData>
<ChangeItem><OriginalData>Type A</OriginalData><NewData>Type B</NewData>

DECLARE @ixml int

EXEC dbo.sp_xml_preparedocument @ixml OUTPUT, @sxml

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

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. Stahl Barry S. Stahl (he/him/his) - Barry is a .NET Software Engineer who has been creating business solutions for enterprise customers for more than 35 years. 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.

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