Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WITH XMLNAMESPACES y subconsultas


WITH XMLNAMESPACES y subconsultas

Author
Message
benito 53825
benito 53825
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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 "@ns1TongueroductModelID",
Name AS "@ns1TongueroductModelName",
(SELECT ProductID AS "data()"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')
) AS "@ns1TongueroductIDs",
(
SELECT ProductID AS "@ns1TongueroductID",
Name AS "@ns1TongueroductName"
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH , type
) AS "ns1TongueroductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');


with result:

<root xmlns="uri2" xmlns:ns1="uri1">
<ProductModelData ns1TongueroductModelID="7" ns1TongueroductModelName="HL Touring Frame" ns1TongueroductIDs="885 887 888 889 890 891 892 893">
<ns1TongueroductNames>
<row xmlns="uri2" xmlns:ns1="uri1" ns1TongueroductID="885" ns1TongueroductName="HL Touring Frame - Yellow, 60" />
<row xmlns="uri2" xmlns:ns1="uri1" ns1TongueroductID="887" ns1TongueroductName="HL Touring Frame - Yellow, 46" />
...
</ns1TongueroductNames>
</ProductModelData>

<ProductModelData ns1TongueroductModelID="9" ns1TongueroductModelName="LL Road Frame" ns1TongueroductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
<ns1TongueroductNames>
<row xmlns="uri2" xmlns:ns1="uri1" ns1TongueroductID="722" ns1TongueroductName="LL Road Frame - Black, 58" />
...
</ns1TongueroductNames>
</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>Wink
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
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2091 Visits: 22787
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
Arthur Olcot
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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 ns1TongueroductModelID {$y/ProductModelID}}
{attribute ns1TongueroductModelName {$y/ProductModelName}}
{attribute ns1TongueroductIDs {$y/ProductIDs}}
<ns1TongueroductNames>
{for $z in $y/ProductNames/row return
<row>
{attribute ns1TongueroductID {$z/ProductID}}
{attribute ns1TongueroductName {$z/ProductName}}
</row>
}
</ns1TongueroductNames>
</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 ns1TongueroductModelID="7" ns1TongueroductModelName="HL Touring Frame" ns1TongueroductIDs="885 887 888 889 890 891 892 893">
<ns1TongueroductNames>
<row ns1TongueroductID="885" ns1TongueroductName="HL Touring Frame - Yellow, 60" />
<row ns1TongueroductID="887" ns1TongueroductName="HL Touring Frame - Yellow, 46" />
<row ns1TongueroductID="888" ns1TongueroductName="HL Touring Frame - Yellow, 50" />
<row ns1TongueroductID="889" ns1TongueroductName="HL Touring Frame - Yellow, 54" />
<row ns1TongueroductID="890" ns1TongueroductName="HL Touring Frame - Blue, 46" />
<row ns1TongueroductID="891" ns1TongueroductName="HL Touring Frame - Blue, 50" />
<row ns1TongueroductID="892" ns1TongueroductName="HL Touring Frame - Blue, 54" />
<row ns1TongueroductID="893" ns1TongueroductName="HL Touring Frame - Blue, 60" />
</ns1TongueroductNames>
</ProductModelData>
<ProductModelData ns1TongueroductModelID="9" ns1TongueroductModelName="LL Road Frame" ns1TongueroductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
<ns1TongueroductNames>
<row ns1TongueroductID="722" ns1TongueroductName="LL Road Frame - Black, 58" />
<row ns1TongueroductID="723" ns1TongueroductName="LL Road Frame - Black, 60" />
<row ns1TongueroductID="724" ns1TongueroductName="LL Road Frame - Black, 62" />
<row ns1TongueroductID="725" ns1TongueroductName="LL Road Frame - Red, 44" />
<row ns1TongueroductID="726" ns1TongueroductName="LL Road Frame - Red, 48" />
<row ns1TongueroductID="727" ns1TongueroductName="LL Road Frame - Red, 52" />
<row ns1TongueroductID="728" ns1TongueroductName="LL Road Frame - Red, 58" />
<row ns1TongueroductID="729" ns1TongueroductName="LL Road Frame - Red, 60" />
<row ns1TongueroductID="730" ns1TongueroductName="LL Road Frame - Red, 62" />
<row ns1TongueroductID="736" ns1TongueroductName="LL Road Frame - Black, 44" />
<row ns1TongueroductID="737" ns1TongueroductName="LL Road Frame - Black, 48" />
<row ns1TongueroductID="738" ns1TongueroductName="LL Road Frame - Black, 52" />
</ns1TongueroductNames>
</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
benito 53825
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search