I'm trying to reproduce something like the following:
<compensatingcontrols xmlns ="http://www.namespace.net/schema.xsd">
<compensatingcontrol>
<id>4</id>
<name>Control1</name>
<description />
<version />
<source>Source Name</source>
<controltext>Some control text.</controltext>
<status>enabled</status>
<validfrom>2007-08-03</validfrom>
<expirationflag>neverexpires</expirationflag>
<expiration>1900-01-01</expiration>
<lastupdatedby>OWNER</lastupdatedby>
<lastupdatedon>2007-09-17</lastupdatedon>
<createdby>OWNER</createdby>
<createdon>2007-08-03</createdon>
<additionalinfo>
<notes xmlns="http://www.namespace.net/schema.xsd" />
</additionalinfo>
<ownerinfo>
<profile>
<id>3</id>
<profilename>OWNER</profilename>
</profile>
</ownerinfo>
</compensatingcontrol>
</compensatingcontrols>
I can produce everything but the root @xmlns and the notes @xmlns attributes using the following query:
SELECT compensatingcontrol.ControlID AS 'compensatingcontrol/id'
, compensatingcontrol.[Control Name] AS 'compensatingcontrol/name'
, NULL AS 'compensatingcontrol/description'
, NULL AS 'compensatingcontrol/version'
, 'START' AS 'compensatingcontrol/source'
, compensatingcontrol.[Control Description] AS 'compensatingcontrol/controltext'
, 'compensatingcontrol/status' =
CASE compensatingcontrol.[Current?]
WHEN 'True' THEN 'enabled'
ELSE 'disabled'
END
, '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'
, NULL AS 'compensatingcontrol/additionalinfo/notes'
, profile.EmpNum AS 'compensatingcontrol/ownerinfo/profile/id'
, profile.EmpUserName AS 'compensatingcontrol/ownerinfo/profile/profilename'
FROM dbo.vwControls compensatingcontrol LEFT OUTER JOIN
dbo.ProcessControl ownerinfo ON compensatingcontrol.ControlID = ownerinfo.ControlID LEFT OUTER JOIN
dbo.vwProcessOwners [profile] ON ownerinfo.ProcessID = [profile].ProcessID
FOR XML PATH('')
, TYPE
, ELEMENTS XSINIL
, ROOT('compensatingcontrols');
Thanks for your help! These namespaces are really giving me grief.