FOR XML PATH and WITH XMLNAMESPACES - how do I NOT output a Namespace Reference for some PATHs?

Question

I have an SQL Server 2005 query, that uses FOR XML PATH... to output the XML. Due to the complexity of the XML being output, I use correlated subqueries to output portions of the XML. Each subquery uses FOR XML PATH... to output composites within that 'chunk' of the XML. This works fine, i.e. the XML is output as I expect it to be/as I need it to be. We also need to use namespaces for some tags. The namespaces work as well - except that the references for the namespaces seem to be output within every composite defined within every FOR XML PATH... statement. I want the references only output within the 'top' or 'root' xml tag/composite, instead of every tag/composite defined within a PATH('...') option somewhere. Another way to say that is, I only want the Namespace references output for the PATH defined within the query itself, NOT for any PATHs defined within subqueries. An example from the query itself is: SELECT ( SELECT PhoneNumber AS '@communicationNumber', 'TELEPHONE' AS '@communicationChannelCode' FROM TableName tn3 WHERE tn3.ID = tn1.ID FOR XML PATH('communicationChannel'), TYPE ), ( SELECT FaxNumber AS '@communicationNumber', 'TELEFAX' AS '@communicationChannelCode' FROM TableName tn2 WHERE tn2.ID = tn1.ID FOR XML PATH('communicationChannel'), TYPE ), 'EN' AS 'personOrDepartmentName/description/language/languageISOCode', PersonName AS 'personOrDepartmentName/description/text' FROM TableName tn1 FOR XML PATH('contact') The query itself is huge, this is just a small part - it is actually a subquery - but it should give the idea of how I have used correlated subqueries each with its own PATH value. Example output is: <communicationChannel xmlns:order="urn:ean.ucc:order:2" xmlns:eanucc="urn:ean.ucc:2" xmlns:sh="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader" communicationNumber="phone number" communicationChannelCode="TELEPHONE" /> <communicationChannel xmlns:order="urn:ean.ucc:order:2" xmlns:eanucc="urn:ean.ucc:2" xmlns:sh="http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader" communicationNumber="fax number" communicationChannelCode="TELEFAX" /> <personOrDepartmentName> <description> <language> <languageISOCode>EN</languageISOCode> </language> <text>name text</text> </description> </personOrDepartmentName> Once again, the output is huge, this is just a small part - but should give an example of the issue here - the namespace references are being output on every tag/composite specified within PATH. The namespace settings are: WITH XMLNAMESPACES ('http://www.unece.org/cefact/namespaces/StandardBusinessDocumentHeader' AS sh, 'urn:ean.ucc:2' AS eanucc, 'urn:ean.ucc:order:2' AS [order]) If the XML was more simple, I could get rid of many of the subqueries and hopefully make the problem go away. But I need to output XML that meets someone else's specifications, so cannot make changes to the structure/shape of the XML output. Is there any way to tell SQL Server 2005, to not output the Namespace references within some PATH values/composites/tags? Or tell it to not output the Namespace references within the PATH values/composites/tags defined within subqueries? Or is there an alternative way to do this? Bearing in mind that I cannot change the structure of the XML itself due to outside constraints/requirements. Thank you!

Answer 1

Have a look at the workaround I posted here:


http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/484d085b-4beb-4568-9bb0-eb82601ade52

Answer 2

wBob, thank you! I could not use the 'query before the main query' option given the weird way that different namespaces were used in different places inside the XML - but the 'convert to VARCHAR', combined with using REPLACE on the VARCHAR variable, worked - while the process is not quite ideal the SQL is still clear and I get what I need. I ended up outputting 'namespace_' within the tags in the SELECT ... FOR XML output, then REPLACEing <namespace_ with <namespace: (and the closing tags as well) to put the namespaces within the appropriate tags. Adding the 'opening' tag as text complete with the namespace references completed the picture, and solved the problem for me! Thanks again! :)

Answer 3

Try using a user defined function for your subquery returning the XML set you need.

Order has many OrderItems

WITH XMLNAMESPACES('http://www.somenamespace.com' as ns0)

SELECT

"Order/OrderID" = o.OrderID,

"Order/OrderItems" = dbo.OrderItemsList(o.OrderID),

''

FROM dbo.Orders

FOR XML PATH ('ns0:Request')

The udf would look like...

CREATE FUNCTION dbo.OrderItemsList (

@OrderID biging

)

RETURNS xml

AS

BEGIN

DECLARE @XMLResult xml

SELECT @XMLResult = (

SELECT

"OrderItem/ID"=oi.OrderItemID,

"OrderItem/Qty"=oi.OrderItemQty,

''

FROM dbo.OrderItem oi

WHERE oi.OrderID = @OrderID

FOR XML PATH ('')

)

RETURN @XMLResult

END

...BTW...sorry for the awful formatting...

HTH and Good luck

...G2

 

---

Greg

Answer 4

Could you please post how do you resolved your problem with an example please, I have a similar problem and I can not resolve it with http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/484d085b-4beb-4568-9bb0-eb82601ade52.

Thanks,

Answer 5

check this thread

Thread

Answer 6

Hi this link can help you, check it for your reference.

http://go4answers.webhost4life.com/Example/xml-namespaces-placement-tsql-respect-178545.aspx