Sample SQL 2000 XML Query Courtesy of AE

Tags: , , | Categories: Development Posted by bsstahl on 5/26/2006 3:53 AM | Comments (0)

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

--------------- ----------------------- -----------------------