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
--- ----- -----
