XML - Eliminate Namespace in Elements
in [Programming SQL]
|
From: Mary on 24 Jun 2010 12:52 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
From: Erland Sommarskog on 24 Jun 2010 18:00 Mary (mary(a)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(a)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
From: John Bell on 25 Jun 2010 04:05 On Thu, 24 Jun 2010 11:52:01 -0500, "Mary" <mary(a)lifetimeinc.com> wrote: >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 > Along with Erlands response you seem to be seeing the same effect as described in https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements The XML should still be valid even though the re-declaration of the namespaces is not necessary. John
|
Pages: 1 Prev: How to retrieve a distinct row using one column as criteria? Next: Subquery with Max(date) |