DPE method error; passing DS persisted As XML

Hello,
I am using Teo Lachev's DPE and have done the following so far.
Added the datasource with 'Nothing' in query. Also the @.datasource parameter
is taking a path to the xsd file that i created off the dataset that i will
use. The designer has the fields setup on the report.
Through code, based on parameters I am forming a dataset that i write to an
xml (this contains the same elements and schema as the xsd).
Then..
DataSet dsDataSet = new DataSet();
dsDataSet.ReadXml(@."C:\FSChartSampleDS.xml", System.Data.XmlReadMode.Auto);
parameters[0] = new ParameterValue();
parameters[0].Name = "DataSource";
parameters[0].Value = dsDataSet.GetXml();
followed by a call to the render function. Here the call fails with this
error:
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
System.NullReferenceException: Object reference not set to an instance of an
object.\n at
AWC.RS.Extensions.DataExtensions.DsDataReader.GetOrdinal(String name)\n at
Microsoft.ReportingServices.DataExtensions.MappingDataReader..ctor(String
dataSetName, IDataReader sourceReader, String[] aliases, String[]
fieldNames)\n at
Microsoft.ReportingServices.ReportProcessing.g..ctor(String A_0, IDataReader
A_1, String[] A_2, String[] A_3)\n at
Microsoft.ReportingServices.ReportProcessing.a0.v()\n at
Microsoft.ReportingServices.ReportProcessing.aw.c()\n at
Microsoft.ReportingServices.ReportProcessing.a0.a(Boolean& A_0)\n at
Microsoft.ReportingServices.ReportProcessing.aw.b()\n at
Microsoft.ReportingServices.ReportProcessing.a0.a(Object A_0)\n -- End of
inner exceptionsolved this myself
"google" wrote:
> Hello,
> I am using Teo Lachev's DPE and have done the following so far.
> Added the datasource with 'Nothing' in query. Also the @.datasource parameter
> is taking a path to the xsd file that i created off the dataset that i will
> use. The designer has the fields setup on the report.
> Through code, based on parameters I am forming a dataset that i write to an
> xml (this contains the same elements and schema as the xsd).
> Then..
> DataSet dsDataSet = new DataSet();
> dsDataSet.ReadXml(@."C:\FSChartSampleDS.xml", System.Data.XmlReadMode.Auto);
> parameters[0] = new ParameterValue();
> parameters[0].Name = "DataSource";
> parameters[0].Value = dsDataSet.GetXml();
> followed by a call to the render function. Here the call fails with this
> error:
> Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
> occurred during report processing. -->
> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
> error has occurred during report processing. -->
> System.NullReferenceException: Object reference not set to an instance of an
> object.\n at
> AWC.RS.Extensions.DataExtensions.DsDataReader.GetOrdinal(String name)\n at
> Microsoft.ReportingServices.DataExtensions.MappingDataReader..ctor(String
> dataSetName, IDataReader sourceReader, String[] aliases, String[]
> fieldNames)\n at
> Microsoft.ReportingServices.ReportProcessing.g..ctor(String A_0, IDataReader
> A_1, String[] A_2, String[] A_3)\n at
> Microsoft.ReportingServices.ReportProcessing.a0.v()\n at
> Microsoft.ReportingServices.ReportProcessing.aw.c()\n at
> Microsoft.ReportingServices.ReportProcessing.a0.a(Boolean& A_0)\n at
> Microsoft.ReportingServices.ReportProcessing.aw.b()\n at
> Microsoft.ReportingServices.ReportProcessing.a0.a(Object A_0)\n -- End of
> inner exception

Don't want escape characters in results

Hi, I have a varchar column containing text which is actually fragments of
HTML.
eg:
<p>sometext</p>
When I retrieve the data using FOR XML I get results with escaped
characters:
<p>sometext</p>
Is there any way to turn off the escaping and just return the actual data?
Thanks!Can you give an example of the query you are using?
You may simply be able to explicitly cast your varchar
column to xml.|||Nice Sunday.
First of all, your HTML string should be well-formed in XML perspective. If
not, resultant XML will be broken. If you have no problem for your string,
just try this script, and see if this makes any sense for you. You may make
some UDF to make the job more convenient.
drop table t1
go
create table t1 (t varchar(10))
insert into t1 values (N'<a/>')
declare @.x xml
set @.x=(select top 1 t from t1)
select @.x
Pohwan Han. Seoul. Have a nice day.
"Paul Robinson" <robinsonpr@.aol.com> wrote in message
news:uCpDPbddGHA.4128@.TK2MSFTNGP05.phx.gbl...
> Hi, I have a varchar column containing text which is actually fragments of
> HTML.
> eg:
> <p>sometext</p>
> When I retrieve the data using FOR XML I get results with escaped
> characters:
> <p>sometext</p>
>
> Is there any way to turn off the escaping and just return the actual data?
> Thanks!
>|||Or convert(),
select convert(xml, t) from t1
Optionally, if your HTML is not well-formed and just want to show the HTML
in browser as HTML, you can use some mid-tier like XSLT with
disable-output-escaping attribute.
Pohwan Han. Seoul. Have a nice day.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23hNpSPydGHA.2416@.TK2MSFTNGP03.phx.gbl...
> Nice Sunday.
> First of all, your HTML string should be well-formed in XML perspective.
> If not, resultant XML will be broken. If you have no problem for your
> string, just try this script, and see if this makes any sense for you. You
> may make some UDF to make the job more convenient.
> drop table t1
> go
> create table t1 (t varchar(10))
> insert into t1 values (N'<a/>')
> declare @.x xml
> set @.x=(select top 1 t from t1)
> select @.x
> --
> Pohwan Han. Seoul. Have a nice day.
> "Paul Robinson" <robinsonpr@.aol.com> wrote in message
> news:uCpDPbddGHA.4128@.TK2MSFTNGP05.phx.gbl...
>|||I'm using SQLServer2000.
An example query is below (there are more columns but I've just cut it down
to one to illustrate the problem:
select 1 AS [Tag], 0 AS [Parent],
varMemo AS [memo!1!!element],
from memo
FOR XML EXPLICIT
Result:
<memo><p>my test data.</p></memo>
The data column which is a varchar contains this:
<p>my test data.</p>
What I want out is:
<memo><p>my test data.</p></memo>
<markc600@.hotmail.com> wrote in message
news:1147450841.895258.275160@.i39g2000cwa.googlegroups.com...
> Can you give an example of the query you are using?
> You may simply be able to explicitly cast your varchar
> column to xml.
>|||Ah slight problem with the xml data type - we're using SQLServer2000. I
just posted the example query in another part of the thread!
Thanks...
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23hNpSPydGHA.2416@.TK2MSFTNGP03.phx.gbl...
> Nice Sunday.
> First of all, your HTML string should be well-formed in XML perspective.
If
> not, resultant XML will be broken. If you have no problem for your string,
> just try this script, and see if this makes any sense for you. You may
make
> some UDF to make the job more convenient.
> drop table t1
> go
> create table t1 (t varchar(10))
> insert into t1 values (N'<a/>')
> declare @.x xml
> set @.x=(select top 1 t from t1)
> select @.x
> --
> Pohwan Han. Seoul. Have a nice day.
> "Paul Robinson" <robinsonpr@.aol.com> wrote in message
> news:uCpDPbddGHA.4128@.TK2MSFTNGP05.phx.gbl...
of
data?
>|||Try changing
varMemo AS [memo!1!!element],
to
varMemo AS [memo!1!!xml],|||That's got it! Many thanks, I appreciate the help!!
<markc600@.hotmail.com> wrote in message
news:1147684296.310383.20930@.u72g2000cwu.googlegroups.com...
> Try changing
> varMemo AS [memo!1!!element],
> to
> varMemo AS [memo!1!!xml],
>

Doing a join between XML data typed field and a relational table in Yukon (SQL 2005)

Hello,
I have a Yukon XML question and would be very happy if someone can
assist:
Let's assume I have 2 tables:
One will be product table Regular relational table with Product ID
as a primary key and some descriptive fields.
Second is an Order table it has Order ID as a PK and Order details
which is a field of XML data type and holds all lines in the order
including quantity, product id, prices, etc.
I want to make a join that will present the orders with all product
details for each order line.
Do you know how can I do a Join between XML field and relational
table?
Thanks,
Nitsan
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
[snip]
> Do you know how can I do a Join between XML field and relational
> table?
You should be able to do this with the new nesting of FOR XML statements in
Yukon. See:
http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
Bryant
|||Thanks for your answer,
I looked into it and it looks like it was doing select on relational tables
and just created XML as an answer.
I am looking for the opposite - Query in XML content join relational table
to get data (even not necessarily in XML format).
Thanks,
Nitsan
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> [snip]
> You should be able to do this with the new nesting of FOR XML statements
in
> Yukon. See:
> http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
> --
> Bryant
>
|||Even outside of the capabilties of FOR XML, you can do what you want.
You can use a value from the XML data type using the method xmltype.value()
in a SQL predicate. Or you can use the value of a sql:variable or sql:column
to do the join inside an XQuery.
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:ccuhjj$kr2$1@.news01.intel.com...
> Thanks for your answer,
> I looked into it and it looks like it was doing select on relational
tables
> and just created XML as an answer.
> I am looking for the opposite - Query in XML content join relational table
> to get data (even not necessarily in XML format).
> Thanks,
> Nitsan
> "Bryant Likes" <bryant@.suespammers.org> wrote in message
> news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> in
>
|||Nitsan,
in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
Preview you can use nodes() method on XML type to get a row set out of an
XML instance and then do the join in T-SQL space:
create table products(ProdID int primary key, ProdDesc nvarchar(max))
go
create table orders(OrderID int primary key, OrdDetails xml)
go
insert products values(1, 'Prod1')
insert products values(2, 'Prod2')
go
insert orders values(1,
'<prod id="1">
<quantity>2</quantity>
<price>12345.67</price>
</prod>
<prod id="2">
<quantity>3</quantity>
<price>9876.54</price>
</prod>')
go
select OrderID, quantity, price, ProdDesc
from
(
select
OrderID,
prod.value('./@.id', 'int') as id,
prod.value('(./quantity)[1]', 'int') as quantity,
prod.value('(./price)[1]', 'money') as price
from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
) as OrdLine
join products on OrdLine.id=products.ProdID
go
You can then apply FOR XML to results if you need results as XML.
Regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> Hello,
> I have a Yukon XML question and would be very happy if someone can
> assist:
> Let's assume I have 2 tables:
> One will be product table - Regular relational table with Product ID
> as a primary key and some descriptive fields.
> Second is an Order table - it has Order ID as a PK and Order details
> which is a field of XML data type and holds all lines in the order
> including quantity, product id, prices, etc.
> I want to make a join that will present the orders with all product
> details for each order line.
> Do you know how can I do a Join between XML field and relational
> table?
> Thanks,
> Nitsan
|||Thanks,
Do you have a short example if usin git for this purpose?
"Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Even outside of the capabilties of FOR XML, you can do what you want.
> You can use a value from the XML data type using the method
xmltype.value()
> in a SQL predicate. Or you can use the value of a sql:variable or
sql:column[vbcol=seagreen]
> to do the join inside an XQuery.
> Bob Beauchemin
> http://staff.develop.com/bobb
>
> "Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
> news:ccuhjj$kr2$1@.news01.intel.com...
> tables
table[vbcol=seagreen]
statements
>
|||Thank you very much !!!
"Eugene Kogan" <eugene_kogan@.hotmail.com> wrote in message
news:uUd0kAJaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Nitsan,
> in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
> Preview you can use nodes() method on XML type to get a row set out of an
> XML instance and then do the join in T-SQL space:
> create table products(ProdID int primary key, ProdDesc nvarchar(max))
> go
> create table orders(OrderID int primary key, OrdDetails xml)
> go
> insert products values(1, 'Prod1')
> insert products values(2, 'Prod2')
> go
> insert orders values(1,
> '<prod id="1">
> <quantity>2</quantity>
> <price>12345.67</price>
> </prod>
> <prod id="2">
> <quantity>3</quantity>
> <price>9876.54</price>
> </prod>')
> go
> select OrderID, quantity, price, ProdDesc
> from
> (
> select
> OrderID,
> prod.value('./@.id', 'int') as id,
> prod.value('(./quantity)[1]', 'int') as quantity,
> prod.value('(./price)[1]', 'money') as price
> from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
> ) as OrdLine
> join products on OrdLine.id=products.ProdID
> go
> You can then apply FOR XML to results if you need results as XML.
>
> Regards,
> Eugene Kogan,
> Technical Lead,
> SQL Server Engine
> Microsoft Corp
> [This posting is provided "AS IS" with no warranties, and confers no
> rights.]
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
>
|||Hi Nitsan,
Here's a really short example, starting with the authors table in the pubs
database:
create table xmlauth (
authbio xml)
go
insert xmlauth values('<author id="172-32-1176"><hobbies><golf score="78"
/><tennis/></hobbies></author>')
insert xmlauth values('<author id="213-46-8915"><hobbies><golf score="82"
/><bowling/></hobbies></author>')
If you know each document contains one author, you can use xml.value:
select authbio.query('/author/hobbies/*') as hobbies, au_lname
from authors, xmlauth
where authors.au_id =
xmlauth.authbio.value('author[1]/@.id', 'varchar(11)')
(if you have a schema collection which specifies a single author occurance,
you can leave out the subscript [1] in the value subquery)
If documents may contain more than one author, (e.g)
insert xmlauth values('
<authors>
<author id="274-80-9391"><hobbies><swimming
/><bowling/></hobbies></author>
<author id="274-80-9391"><hobbies><baseball
/><bowling/></hobbies></author>
</authors>')
you would need xml.nodes. Eugene's excellent example illustrates that.
You can even do this (caution: NOT recommended, cartesian product) using the
SQL value in the XQuery:
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where hobbies.exist('/*')=1
or
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where convert(nvarchar(100), hobbies) <> ''
Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:cd034u$fo8$1@.news01.intel.com...
> Thanks,
> Do you have a short example if usin git for this purpose?
> "Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
> news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> xmltype.value()
> sql:column
> table
> statements
>

does the XML Task update XML files?

I'm using package configurations to store my server/database name in an XML File. I need to be able to dynamically change the database at runtime when I execute the package. Can I use the XML Task in another package to make a change to that xml file? I'm not familiar at all with XML, so I don't really know the syntax for XPath or XSLT or anything. Basically I'm just looking for an example of how to this with XML, but I haven't found anything on the web to explain this to me. BOL isn't very helpful with the XML Task.
I'm using the June CTP, but also have access to the Sept CTP.
Thanks,
-Andy

If you save the results as a file, yes.
K|||but can I overwrite the source file? Do you have any examples of this or point me to one? I don't see on in BOL or in the Samples that come with the CTP's.
Thanks,
-Andy|||

badandy:

did you ever get over this problem? i have come across the same problem and not sure how to do it. If you did please let me know

Thanks

hegde

does the XML Task update XML files?

I'm using package configurations to store my server/database name in an XML File. I need to be able to dynamically change the database at runtime when I execute the package. Can I use the XML Task in another package to make a change to that xml file? I'm not familiar at all with XML, so I don't really know the syntax for XPath or XSLT or anything. Basically I'm just looking for an example of how to this with XML, but I haven't found anything on the web to explain this to me. BOL isn't very helpful with the XML Task.
I'm using the June CTP, but also have access to the Sept CTP.
Thanks,
-Andy

If you save the results as a file, yes.
K|||but can I overwrite the source file? Do you have any examples of this or point me to one? I don't see on in BOL or in the Samples that come with the CTP's.
Thanks,
-Andy|||

badandy:

did you ever get over this problem? i have come across the same problem and not sure how to do it. If you did please let me know

Thanks

hegde