WITH XMLNAMESPACES y subconsultas

Question

WITH XMLNAMESPACES y subconsultas

benito 53825

SSC Journeyman

Points: 84
More actions
August 8, 2012 at 5:06 am

#279935

hi,
i have a problem with xmlnamespaces. i try to explain my problem using a little example (information extracted from http://msdn.microsoft.com/en-us/library/bb510462.aspx)
In this link, i was searching to create a value list with nested elements (with subquerys into the principal query), and adding a namespace in the resulting XML. The result in this link is:
USE AdventureWorks2012;
GO
WITH XMLNAMESPACES ('uri1' AS ns1, DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
Name AS "@ns1:ProductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')
) AS "@ns1:ProductIDs",
(
SELECT ProductID AS "@ns1:ProductID",
Name AS "@ns1:ProductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH , type
) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');
with result:
<root xmlns="uri2" xmlns:ns1="uri1">
<ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
<ns1:ProductNames>
<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
...
</ns1:ProductNames>
</ProductModelData>
<ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
<ns1:ProductNames>
<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
...
</ns1:ProductNames>
</ProductModelData>
</root>
Well, in this point, xml namespaces(xmlns="uri2" xmlns:ns1="uri1") is added at beggining, but also into nested elements (into tag <ProductModelData> --> tags <row>)
I need that xmlnamespace only affects to the principal tag (in this example, in tag <root> : <root xmlns="uri2" xmlns:ns1="uri1">
PD: sorry for my bad english. I have tried to use an example to explain what i need.
Regards,
Beni

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Known issue. I don't believe there's a simple workaround.

http://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 2

As Mark stated, there is no easy work-around. However, I have had to do this myself in the past and the way that I solved it was by using XQuery, specifically flwor so that I had quite granular control on the construction of the xml. So if i was to take your example query (i developed this against AdventureWorks2008R2 by the way) and put it through the approach that had to take, it would look like the following:

with xmlnamespaces (DEFAULT 'uri2')

SELECT XmlStructure.query('declare default element namespace "uri2";

for $x in /root return

<root xmlns="uri2" xmlns:ns1="uri1">

{

for $y in $x/ProductModelData return

<ProductModelData>

{attribute ns1:ProductModelID {$y/ProductModelID}}

{attribute ns1:ProductModelName {$y/ProductModelName}}

{attribute ns1:ProductIDs {$y/ProductIDs}}

<ns1:ProductNames>

{for $z in $y/ProductNames/row return

<row>

{attribute ns1:ProductID {$z/ProductID}}

{attribute ns1:ProductName {$z/ProductName}}

</row>

}

</ns1:ProductNames>

</ProductModelData>

}

</root>')

FROM

(

SELECT

(

SELECT ProductModelID AS "ProductModelID",

Name AS "ProductModelName",

(

SELECT ProductID AS "data()"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH ('')

) AS "ProductIDs",

(

SELECT ProductID AS "ProductID",

Name AS "ProductName"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH, type

) AS "ProductNames"

FROM Production.ProductModel

WHERE ProductModelID= 7 OR ProductModelID=9

FOR XML PATH('ProductModelData'), root('root'), TYPE

) XmlStructure

) x

basically, what I am doing is creating an xml blob with the sort of structure that i want to return but without the namespaces and then with that, I am using XQuery to essentially create it again with the namespaces at the top level and not all over the place. Running the above code on my instance gives me the following xml:

<root xmlns="uri2" xmlns:ns1="uri1">

<ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">

<ns1:ProductNames>

<row ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />

<row ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />

<row ns1:ProductID="888" ns1:ProductName="HL Touring Frame - Yellow, 50" />

<row ns1:ProductID="889" ns1:ProductName="HL Touring Frame - Yellow, 54" />

<row ns1:ProductID="890" ns1:ProductName="HL Touring Frame - Blue, 46" />

<row ns1:ProductID="891" ns1:ProductName="HL Touring Frame - Blue, 50" />

<row ns1:ProductID="892" ns1:ProductName="HL Touring Frame - Blue, 54" />

<row ns1:ProductID="893" ns1:ProductName="HL Touring Frame - Blue, 60" />

</ns1:ProductNames>

</ProductModelData>

<ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">

<ns1:ProductNames>

<row ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />

<row ns1:ProductID="723" ns1:ProductName="LL Road Frame - Black, 60" />

<row ns1:ProductID="724" ns1:ProductName="LL Road Frame - Black, 62" />

<row ns1:ProductID="725" ns1:ProductName="LL Road Frame - Red, 44" />

<row ns1:ProductID="726" ns1:ProductName="LL Road Frame - Red, 48" />

<row ns1:ProductID="727" ns1:ProductName="LL Road Frame - Red, 52" />

<row ns1:ProductID="728" ns1:ProductName="LL Road Frame - Red, 58" />

<row ns1:ProductID="729" ns1:ProductName="LL Road Frame - Red, 60" />

<row ns1:ProductID="730" ns1:ProductName="LL Road Frame - Red, 62" />

<row ns1:ProductID="736" ns1:ProductName="LL Road Frame - Black, 44" />

<row ns1:ProductID="737" ns1:ProductName="LL Road Frame - Black, 48" />

<row ns1:ProductID="738" ns1:ProductName="LL Road Frame - Black, 52" />

</ns1:ProductNames>

</ProductModelData>

</root>

I'm not saying that this is ideal, and i'm not sure how much extra overhead is required to do this, but it is possible - just not quick/easy!

Hope it helps.

benito 53825 SSC Journeyman Points: 84 More actions · Answer 3

thanks a lot. I didn't found any information on web

I still have not tried the solution, but i think that i can take this information and resolve this problem.