Ryan,
run this query:
(excuse the bad formatting)
WITH
XMLNAMESPACES (
DEFAULT 'http://www.namespace.net/yourschema.xsd'
)
SELECT
'compensation control ID' AS 'compensatingcontrol/id' ,
'compensation control name'
AS 'compensatingcontrol/name' ,
NULL
AS 'compensatingcontrol/description' ,
NULL
AS 'compensatingcontrol/version' ,
'START'
AS 'compensatingcontrol/source' ,
'compensation control description'
AS 'compensatingcontrol/controltext' ,
'true'
as 'compensatingcontrol/status',
'2007-01-01'
AS 'compensatingcontrol/validfrom' ,
'neverexpires'
AS 'compensatingcontrol/expirationflag' ,
'1900-01-01'
AS 'compensatingcontrol/expiration' ,
'GRMalone'
AS 'compensatingcontrol/lastupdatedby' ,
'2007-06-01'
AS 'compensatingcontrol/lastupdatedon' ,
'GRMalone'
AS 'compensatingcontrol/createdby' ,
'2007-01-01'
AS 'compensatingcontrol/createdon' ,
(
SELECT 'this is a note'
FOR XML PATH('notes'), TYPE
) as 'compensatingcontrol/additionalinfo',
--NULL AS 'c:compensatingcontrol/additionalinfo/notes' ,
'empnum'
AS 'compensatingcontrol/ownerinfo/profile/id' ,
'empusername'
AS 'compensatingcontrol/ownerinfo/profile/profilename'
FOR
XML PATH('') , TYPE , ELEMENTS XSINIL , ROOT('compensatingcontrols');
this will give the following results:
<
compensatingcontrols xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.namespace.net/yourschema.xsd">
<
compensatingcontrol>
<
id>compensation control ID</id>
<
name>compensation control name</name>
<
description xsi:nil="true" />
<
version xsi:nil="true" />
<
source>START</source>
<
controltext>compensation control description</controltext>
<
status>true</status>
<
validfrom>2007-01-01</validfrom>
<
expirationflag>neverexpires</expirationflag>
<
expiration>1900-01-01</expiration>
<
lastupdatedby>GRMalone</lastupdatedby>
<
lastupdatedon>2007-06-01</lastupdatedon>
<
createdby>GRMalone</createdby>
<
createdon>2007-01-01</createdon>
<
additionalinfo>
<
notes xmlns="http://www.namespace.net/yourschema.xsd">this is a note</notes>
</
additionalinfo>
<
ownerinfo>
<
profile>
<
id>empnum</id>
<
profilename>empusername</profilename>
</
profile>
</
ownerinfo>
</
compensatingcontrol>
</
compensatingcontrols>
.