<TEST_DATA xmlns="http://www.TestData.com/Schema/Test" 
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
		xmlns:schemaLocation="http://www.TestData.com/Schema/Test_Schema.xsd">
  <KEY _Name="ZID" _Value="789" _ID="ZID" />
  <ZL>
    <ZLN>1234</ZLN>
    <ZTLA>200000</ZTLA>
    <ZMR>8.50</ZMR>
    <ZCLS>CART</ZCLS>
    <ZPITIP>1500.0000</ZPITIP>
  </ZL>
  <ZBS>
    <ZB>
      <ZBN>TEST Test</ZBN>
      <ZFN>TEST</ZFN>
      <ZMN></ZMN>
      <ZLL>Test</ZLL>
    </ZB>
  </ZBS>
  <ZCC>
    <ZC>
      <ZSN>1</ZSN>
      <ZCE>4</ZCE>
    </ZC>
  </ZCC>
  <ZP>
    <ZPT>Attached</ZPT>
    <ZPP>6500</ZPP>
    <ZNU>3</ZNU>
  </ZP>
</TEST_DATA>

create table #ZDL(ZLN bigint, ZTLA int, ZMR decimal(18,2), ZCLS varchar(20), ZPITIP money)
insert into #ZDL values(1234, 200000, 8.5, 'CART', 1500.00)

create table #ZBL(ZLN bigint, ZBN varchar(50), ZFN varchar(20), ZMN varchar(8), ZLL varchar(20))
insert into #ZBL values(1234, 'TEST Test', 'TEST', null, 'Test')

create table #ZCC(ZLN bigint, ZSN int, ZCE int)
insert into #ZCC values(1234, 1, 4)

create table #ZP(ZLN bigint, ZPT varchar(50), ZPP int, ZNU int)
insert into #ZP values(1234, 'Attached', 6500, 3)


;WITH XMLNAMESPACES ('http://www.TestData.com/Schema/Test_Schema.xsd' as schemaLocation,
						'http://www.w3.org/2001/XMLSchema-instance' as xsi,
					DEFAULT 'http://www.TestData.com/Schema/Test')	
SELECT
	'ZID' AS [KEY/@_Name]
	,789 AS [KEY/@_Value]
	,'ZID' AS [KEY/@_ID]

	,ZDL.ZLN AS [ZL/ZLN]
	,ZDL.ZTLA AS [ZL/ZTLA]
	,ZDL.ZMR AS [ZL/ZMR]
	,ZDL.ZCLS AS [ZL/ZCLS]
	,ZDL.ZPITIP AS [ZL/ZPITIP]

	,(
		SELECT
				ZBL.ZBN AS [ZBN]
				,ZBL.ZFN AS [ZFN]
				,ISNULL(ZBL.ZMN,'') AS [ZMN]
				,ZBL.ZLL AS [ZLL]
			FROM #ZBL AS ZBL
			WHERE
				ZBL.ZLN = ZDL.ZLN			
			FOR	XML PATH('ZB'),TYPE
	) AS 'ZBS'



	,(
		SELECT
				ZCC.ZSN AS [ZSN]
				,ZCC.ZCE AS [ZCE]				
			FROM #ZCC AS ZCC
			WHERE
				ZCC.ZLN = ZDL.ZLN			
			FOR	XML PATH('ZC'),TYPE
	) AS 'ZCC'
	

	,(
			SELECT
				ZP.ZPT AS [ZPT]
				,ZP.ZPP AS [ZPP]
				,ZP.ZNU AS [ZNU]			
			FROM #ZP ZP
			WHERE
				ZP.ZLN = ZDL.ZLN			
			FOR	XML PATH('ZP'),TYPE
	) --'ZP'
	

	FROM
		#ZDL ZDL
		--INNER JOIN #ZBL ZBL ON ZDL.ZLN=ZBL.ZLN
		--INNER JOIN #ZCC ZCC ON ZDL.ZLN=ZCC.ZLN
		--INNER JOIN #ZP ZP ON ZDL.ZLN=ZP.ZLN
	FOR XML PATH('') , ELEMENTS XSINIL , ROOT('TEST_DATA')

DROP TABLE #ZDL;
DROP TABLE #ZBL;
DROP TABLE #ZCC;
DROP TABLE #ZP;

<TEST_DATA xmlns="http://www.TestData.com/Schema/Test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://www.TestData.com/Schema/Test_Schema.xsd">
  <KEY _Name="ZID" _Value="789" _ID="ZID" />
  <ZL>
    <ZLN>1234</ZLN>
    <ZTLA>200000</ZTLA>
    <ZMR>8.50</ZMR>
    <ZCLS>CART</ZCLS>
    <ZPITIP>1500.0000</ZPITIP>
  </ZL>
  <ZBS>
    <ZB xmlns="http://www.TestData.com/Schema/Test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://www.TestData.com/Schema/Test_Schema.xsd">
      <ZBN>TEST Test</ZBN>
      <ZFN>TEST</ZFN>
      <ZMN />
      <ZLL>Test</ZLL>
    </ZB>
  </ZBS>
  <ZCC>
    <ZC xmlns="http://www.TestData.com/Schema/Test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://www.TestData.com/Schema/Test_Schema.xsd">
      <ZSN>1</ZSN>
      <ZCE>4</ZCE>
    </ZC>
  </ZCC>
  <ZP xmlns="http://www.TestData.com/Schema/Test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://www.TestData.com/Schema/Test_Schema.xsd">
    <ZPT>Attached</ZPT>
    <ZPP>6500</ZPP>
    <ZNU>3</ZNU>
  </ZP>
</TEST_DATA>

;WITH XMLNAMESPACES...