XML - Eliminate Namespace in Elements

I have created a stored procedure that generates an XML file. The only issue
is that the namespace is being duplicated in each of the detail elements and
I only want it to appear in the root. I am including my code and any help
would be greatly appreciated!

TIA, Mary

This is just a brief display of what I am trying to produce .....

<submission namespace data .....>

<header_record>
detail elements
</header_record>

<detail_record ID="123">
detail elements
</detail_record>
<detail_record ID="124">
detail elements
</detail_record>

</submission>



;WITH XMLNAMESPACES (
DEFAULT 'http://www.wisconsinedi.org',
'http://www.wisconsinedi.org' as "fc",
'http://www.w3.org/2001/XMLSchema-instance' as "xsi")


SELECT

(SELECT
submitter_organization_id,
CONVERT(varchar(10), GETDATE(), 20) AS submission_date,
@StartDate AS begin_posting_date,
@EndDate AS end_posting_date,
COUNT(record_id) AS number_of_records_transmitted

FROM dbo.vw_EncounterAll AS header_record
WHERE posting_date BETWEEN @StartDate And @EndDate
GROUP BY submitter_organization_id
FOR XML PATH('header_record'), TYPE),

(SELECT
record_id AS "@ID",
billing_provider_last_name,
billing_provider_first_name,
adjustment_type,
billing_provider_id,
billing_provider_id_qualifier,
charges,
claim_status,
data_source,
member_share,
medicare_paid_amount,
original_id,
other_payer_paid_amount_primary,
other_payer_paid_amount_secondary,
paid_amount,
parent_record_id,
place_of_service,
procedure_code,
quantity,
posting_date,
reciept_date,
recipient_death_date,
recipient_first_name,
recipient_last_name,
recipient_id,
record_type,
service_date_from,
service_date_to,
spc,
submitter_organization_id,
support_indicator,
unit_or_basis_for_measurement_code

FROM vw_EncounterAll AS detail_record WHERE BatchID = @BatchID
FOR XML PATH ('detail_record'), TYPE)

FOR XML PATH(''), ROOT('submission'), TYPE

Mary (mary@lifetimeinc.com) writes:
> I have created a stored procedure that generates an XML file. The only
> issue is that the namespace is being duplicated in each of the detail
> elements and I only want it to appear in the root. I am including my
> code and any help would be greatly appreciated!
>
> TIA, Mary
>
> This is just a brief display of what I am trying to produce .....
>
><submission namespace data .....>
>
> <header_record>
> detail elements
> </header_record>
>
> <detail_record ID="123">
> detail elements
> </detail_record>
> <detail_record ID="124">
> detail elements
> </detail_record>
>
></submission>

This is not an area that I know too well. But by putting the inner
XML documents in variable, I was able to reduce the presence of the
namespaces in the inner nodes, but not elimiate it:

declare @x1 xml, @x2 xml

SELECT @x1 =

(SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderID = 11000
FOR XML PATH('header_record'), TYPE),
@x2 =
(SELECT OrderID AS "@ID", ProductID, Quantity
FROM [Order Details]
WHERE OrderID = 11000
FOR XML PATH('detail_record'), TYPE)

;WITH XMLNAMESPACES (
DEFAULT 'http://www.wisconsinedi.org',
'http://www.wisconsinedi.org' as "fc",
'http://www.w3.org/2001/XMLSchema-instance' as "xsi")
SELECT @x1, @x2
FOR XML PATH(''), ROOT('submission')

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx