Generating a value list using PATH mode

I am attempting to get data using the "for xml path" and would like to list my data in child elements. I can render the data in the format that I would want but instead of creating child elements I am left with HTML like character codes around my element name (&lt and &gt).

I am following the example outlined in http://msdn2.microsoft.com/en-us/library/ms189885.aspx section C. The example unfortunately even shows the &lt and &gt symbols. I am missing something obvious here? Any help would be greatly appreciated.

Are you using a nested query? If so add the TYPE directive to the FOR XML e.g.

Code Snippet

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 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')

that way the XML is not escaped.

Generating a namespace prefix with For XML Explicit

Hello,
How do I get my FOR XML explicit to qualify the returned elements with a
namespace prefix?
I want:
<s1:NewOrderLine xmlns:s1 ="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
not:
<NewOrderLine>
<Quantity>23</Quantity>
<NewOrderLine>
Thanks -
Simon
In SQL Server 2000, you need to do it explicitly add them.
Something like (if you are using auto mode):
select a as "s1:a", 'uri:example' as "xmlns:s1"
from t as "s1:t"
for xml auto
if you want to make element-centric mappings, you will need to use the
EXPLICIT mode.
Note in SQL Server 2005, there will be an easier way to specify XML
namespaces using the WITH XMLNAMESPACES clause.
Best regards
Michael
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
> Hello,
> How do I get my FOR XML explicit to qualify the returned elements with a
> namespace prefix?
> I want:
> <s1:NewOrderLine xmlns:s1 ="www.company.com">
> <s1:Quantity>23</s1:Quantity>
> </s1:NewOrderLine>
> not:
> <NewOrderLine>
> <Quantity>23</Quantity>
> <NewOrderLine>
> Thanks -
> Simon
>
|||Thanks for the reply, Michael. I wonder if you can elaborate on the EXPLICIT
mode option - with the query below I can get these results, but as you can
see, what I don't have is the 'xmlns:s1 ="www.company.com" in the root
element. Is it possible to change the query below to get that, or,
alternately, is it possible to load this result into an XMLDocument and then
add the namespace declaration?
<ns:SimpleTest>
<ns:Customers>
<ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
</ns:Customers>
</ns:SimpleTest>
from query:
select 1 as Tag,
Null as Parent,
Null as [ns:Sample!1!!element],
Null as [ns:Customers!2!ns:CompanyName!element],
Null as [ns:Customers!2!ns:CustomerID!hide]
UNION ALL
select 2 as Tag,
1 as Parent ,
Null as [ns:Sample!1!!element],
[Customers1].[CompanyName] as [ns:Customers!2!ns:CompanyName!element],
[Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
FROM
(select
[Customers].[CompanyName] as [CompanyName],
[Customers].[CustomerID] as [CustomerID]
from [Customers])
as [Customers1]
Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide] , Tag
FOR XML EXPLICIT
"Michael Rys [MSFT]" wrote:

> In SQL Server 2000, you need to do it explicitly add them.
> Something like (if you are using auto mode):
> select a as "s1:a", 'uri:example' as "xmlns:s1"
> from t as "s1:t"
> for xml auto
> if you want to make element-centric mappings, you will need to use the
> EXPLICIT mode.
> Note in SQL Server 2005, there will be an easier way to specify XML
> namespaces using the WITH XMLNAMESPACES clause.
> Best regards
> Michael
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
>
>
|||Simon,
I think the trick you are looking for is to treat xmlns as an
attribute:
Here is an example based on your request:
select 1 as tag, null as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
null as [s1:Quantity!2]
UNION
select 2 as tag, 1 as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
23 as [s1:Quantity!2]
ORDER BY TAG,PARENT
FOR XML EXPLICIT
This would produce the following:
<s1:NewOrderLine xmlns:s1="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
HTH,
Todd
Simon wrote:
> Thanks for the reply, Michael. I wonder if you can elaborate on the
EXPLICIT
> mode option - with the query below I can get these results, but as
you can
> see, what I don't have is the 'xmlns:s1 ="www.company.com" in the
root
> element. Is it possible to change the query below to get that, or,
> alternately, is it possible to load this result into an XMLDocument
and then
> add the namespace declaration?
> <ns:SimpleTest>
> <ns:Customers>
> <ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
> </ns:Customers>
> </ns:SimpleTest>
> from query:
> select 1 as Tag,
> Null as Parent,
> Null as [ns:Sample!1!!element],
> Null as [ns:Customers!2!ns:CompanyName!element],
> Null as [ns:Customers!2!ns:CustomerID!hide]
> UNION ALL
> select 2 as Tag,
> 1 as Parent ,
> Null as [ns:Sample!1!!element],
> [Customers1].[CompanyName] as
[ns:Customers!2!ns:CompanyName!element],
> [Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
> FROM
> (select
> [Customers].[CompanyName] as [CompanyName],
> [Customers].[CustomerID] as [CustomerID]
> from [Customers])
> as [Customers1]
> Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide]
, Tag[vbcol=seagreen]
> FOR XML EXPLICIT
> "Michael Rys [MSFT]" wrote:
the[vbcol=seagreen]
[vbcol=seagreen]
with a[vbcol=seagreen]

Generating a namespace prefix with For XML Explicit

Hello,
How do I get my FOR XML explicit to qualify the returned elements with a
namespace prefix?
I want:
<s1:NewOrderLine xmlns:s1 ="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
not:
<NewOrderLine>
<Quantity>23</Quantity>
<NewOrderLine>
Thanks -
SimonIn SQL Server 2000, you need to do it explicitly add them.
Something like (if you are using auto mode):
select a as "s1:a", 'uri:example' as "xmlns:s1"
from t as "s1:t"
for xml auto
if you want to make element-centric mappings, you will need to use the
EXPLICIT mode.
Note in SQL Server 2005, there will be an easier way to specify XML
namespaces using the WITH XMLNAMESPACES clause.
Best regards
Michael
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
> Hello,
> How do I get my FOR XML explicit to qualify the returned elements with a
> namespace prefix?
> I want:
> <s1:NewOrderLine xmlns:s1 ="www.company.com">
> <s1:Quantity>23</s1:Quantity>
> </s1:NewOrderLine>
> not:
> <NewOrderLine>
> <Quantity>23</Quantity>
> <NewOrderLine>
> Thanks -
> Simon
>|||Thanks for the reply, Michael. I wonder if you can elaborate on the EXPLICIT
mode option - with the query below I can get these results, but as you can
see, what I don't have is the 'xmlns:s1 ="www.company.com" in the root
element. Is it possible to change the query below to get that, or,
alternately, is it possible to load this result into an XMLDocument and then
add the namespace declaration?
<ns:SimpleTest>
<ns:Customers>
<ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
</ns:Customers>
</ns:SimpleTest>
from query:
select 1 as Tag,
Null as Parent,
Null as [ns:Sample!1!!element],
Null as [ns:Customers!2!ns:CompanyName!element],
Null as [ns:Customers!2!ns:CustomerID!hide]
UNION ALL
select 2 as Tag,
1 as Parent ,
Null as [ns:Sample!1!!element],
[Customers1].[CompanyName] as [ns:Customers!2!ns:CompanyName!element],
[Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
FROM
(select
[Customers].[CompanyName] as [CompanyName],
[Customers].[CustomerID] as [CustomerID]
from [Customers])
as [Customers1]
Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide] , Tag
FOR XML EXPLICIT
"Michael Rys [MSFT]" wrote:

> In SQL Server 2000, you need to do it explicitly add them.
> Something like (if you are using auto mode):
> select a as "s1:a", 'uri:example' as "xmlns:s1"
> from t as "s1:t"
> for xml auto
> if you want to make element-centric mappings, you will need to use the
> EXPLICIT mode.
> Note in SQL Server 2005, there will be an easier way to specify XML
> namespaces using the WITH XMLNAMESPACES clause.
> Best regards
> Michael
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:418B0654-6075-4FB6-8C2C-845B65E1E221@.microsoft.com...
>
>|||Simon,
I think the trick you are looking for is to treat xmlns as an
attribute:
Here is an example based on your request:
select 1 as tag, null as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
null as [s1:Quantity!2]
UNION
select 2 as tag, 1 as parent,
'www.company.com' as [s1:NewOrderLine!1!xmlns:s1],
23 as [s1:Quantity!2]
ORDER BY TAG,PARENT
FOR XML EXPLICIT
This would produce the following:
<s1:NewOrderLine xmlns:s1="www.company.com">
<s1:Quantity>23</s1:Quantity>
</s1:NewOrderLine>
HTH,
Todd
Simon wrote:
> Thanks for the reply, Michael. I wonder if you can elaborate on the
EXPLICIT
> mode option - with the query below I can get these results, but as
you can
> see, what I don't have is the 'xmlns:s1 ="www.company.com" in the
root
> element. Is it possible to change the query below to get that, or,
> alternately, is it possible to load this result into an XMLDocument
and then
> add the namespace declaration?
> <ns:SimpleTest>
> <ns:Customers>
> <ns:CompanyName>Alfreds Futterkiste</ns:CompanyName>
> </ns:Customers>
> </ns:SimpleTest>
> from query:
> select 1 as Tag,
> Null as Parent,
> Null as [ns:Sample!1!!element],
> Null as [ns:Customers!2!ns:CompanyName!element],
> Null as [ns:Customers!2!ns:CustomerID!hide]
> UNION ALL
> select 2 as Tag,
> 1 as Parent ,
> Null as [ns:Sample!1!!element],
> [Customers1].[CompanyName] as
[ns:Customers!2!ns:CompanyName!element],

> [Customers1].[CustomerID] as [ns:Customers!2!ns:CustomerID!hide]
> FROM
> (select
> [Customers].[CompanyName] as [CompanyName],
> [Customers].[CustomerID] as [CustomerID]
> from [Customers])
> as [Customers1]
> Order By [ns:Sample!1!!element] , [ns:Customers!2!ns:CustomerID!hide]
, Tag
> FOR XML EXPLICIT
> "Michael Rys [MSFT]" wrote:
>
the
with a

Generated RDL Causes Elements to Overlap

Howdy,
I'm generating a series of RDL reports using Python and Cheetah. It
seems to work great with one, huge problem: when I go to view the
reports in Visual Studio or when they're deployed, the report elements
overlap or are out of order.
Is there any non-absolute way to position elements using RDL? I can't
figure out how to even take just 3 textboxes and get them to flow down
a page without specifying their TOP coordinates. Sounds basic, but I'm
butting my head against the wall here. At best, they seem to flow in
reverse order.
Here's an example of some simple RDL that ends up overlapping (in
Visual Studio, which also warns me about overlapping) - any help at
all, conceptually or otherwise, would be much appreciated:
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test box 1</Value>
</Textbox>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 2</Value>
</Textbox>
<Textbox Name="textbox3">
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 3</Value>
</Textbox>
</ReportItems>My suggestion is to post this in the web forum. The newsgroup is pretty much
devoid of MS people and this is a pretty specific and unusual question, very
few people have generated RDL themselves. If you go to the web forum not
only might a MS person be hanging out there and provide an answer, the other
RS MVP hangs out there and he might be able to help you.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=82&siteid=1
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<moviegoer22@.gmail.com> wrote in message
news:1185472600.783495.246480@.22g2000hsm.googlegroups.com...
> Howdy,
> I'm generating a series of RDL reports using Python and Cheetah. It
> seems to work great with one, huge problem: when I go to view the
> reports in Visual Studio or when they're deployed, the report elements
> overlap or are out of order.
> Is there any non-absolute way to position elements using RDL? I can't
> figure out how to even take just 3 textboxes and get them to flow down
> a page without specifying their TOP coordinates. Sounds basic, but I'm
> butting my head against the wall here. At best, they seem to flow in
> reverse order.
> Here's an example of some simple RDL that ends up overlapping (in
> Visual Studio, which also warns me about overlapping) - any help at
> all, conceptually or otherwise, would be much appreciated:
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test box 1</Value>
> </Textbox>
> <Textbox Name="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 2</Value>
> </Textbox>
> <Textbox Name="textbox3">
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 3</Value>
> </Textbox>
> </ReportItems>
>