Hi Ryan,

please post a sample xml structure that you need. I will then try to write a query for that.

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.

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:

DECLARE @t TABLE (
	Acno VARCHAR(20),
	acdesc VARCHAR(20),
	actype CHAR(1)
)
INSERT INTO @t (acno, acdesc, actype) 
SELECT '1122334', 'Tesr acct', 'C' UNION ALL
SELECT '0004455', 'Bank Test Account', 'S'

SELECT
	'12345' AS Business_ID,
	(
		SELECT
			acno AS ACCOUNT_NUMBER,
			acdesc AS ACCOUNT_DESCRIPTION,
			actype AS ACCOUNT_TYPE
		FROM @t
		FOR XML PATH('Account'),ROOT('Accounts'), TYPE
	)
FOR XML PATH(''), ROOT('Business')
/*
<Business>
	<Business_ID>12345</Business_Id>
	<Accounts>
		<Account>
			<ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER>
			<ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION>
			<ACCOUNT_TYPE>C</ACCOUNT_TYPE>
		</Account>
		<Account>
			<ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER>
			<ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION>
			<ACCOUNT_TYPE>S</ACCOUNT_TYPE>
		</Account>
	</Accounts>
</Business>
*/

SELECT (
	select 
		*,
		(
			select * from account a
			where a.business_id = b.business_id
			for xml path('account'), root('accounts'), type
		)
	from business b
	for xml path(''), root('Small_Business'),TYPE
)
FOR XML PATH('Business_Type'), ROOT('XMl')