Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

WITH XMLNAMESPACES y subconsultas Expand / Collapse
Author
Message
Posted Wednesday, August 8, 2012 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 4:00 AM
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 "@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
Post #1341789
Posted Wednesday, August 8, 2012 5:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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


____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1341803
Posted Wednesday, August 8, 2012 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:29 AM
Points: 2,554, Visits: 1,615
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.
Post #1341874
Posted Thursday, August 9, 2012 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 4:00 AM
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.

Post #1342516
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse