Extracting Sql: rowset

Showing posts with label rowset. Show all posts

Thursday, March 29, 2012

Failed to Open Rowset Error in Crystal Reports

hello all,

i have developed a desktop application(software) inwhich used Crystal Reports 9. I am using Microsoft Developement Environment 2003 (Visual Studio 2003).The reports in this software runs well on my own system where i developed the software but when I install this software on the computer(client computer), the report does not open when the Generate Report button is clicked. This button click event fills the data using a simple query from databse(SQL Server 2000) and this error is displayed:

Failed to open Rowset.

The .Net Framework 1.1 is installed on the client computer before the software is installed and run.

I also include some merge modules in the setup of the project that I make. These merge modules are:

Crystal_Database_Access_enu2003.msm

Crystal_regwiz2003.msn

Crystal_Database_Access2003.msm

Crystal_managed2003.msm

Please help me to resolve this problem so that on the client computer the reports are displayed and data is shown.

Thanks in advance

hello all again,

I posted this problem yesterday and got no reply yet.

please help me solve this problem. I am in great need of this reporting software but don't know what is missing or wrong which is creating problem

Thanks all of you!

|||

Hi,

I have the exact problem. If some one can reply it will be great.

Thanx.

|||This sounds like a Crystal Reports issue. This forum is for questions regarding SQL Server Reporting Services.

failed to open rowset

i've been received this error while i want to preview my report.

Failed to open rowset.
[Microsoft][ODBC SQLServer Driver] Communication link failure.

why is this happen? and what should i do? when i check with query analyzer, all the data do appear.

thanks.

regards.Open the Report and Do Verify database|||Open the Report and Do Verify database

thank u so much madhi :)|||Did you solve the problem?|||Did you solve the problem?

i did.. and after make some correction on sql command, my program now successfully running :D

thank u..

have a nice day;)

Failed to open a rowset Warning/Error

Hi there

I am working in CR 9.0 and have included subreports in the main report.

Whenever I run the main report and have to give the Login ID & password for the " HiT ODBC / 400 Driver" (DataSource - AS400) I get the following warning/error.

"Failed to open a rowset.
Details: HY000:{HiT][HiT ODBC/400][SQL/400][ODBS Error][SQL0128] Use of NULL is not valid"

If I click the OK button the report is generated.

If I run the same report the second/third time - where I do not have to specify the Login ID & password the error/warning does not occur.

How do I get rid of this warning/error msg.

rkpYou need to pass them in the Front End coding

Failed to open a rowset (Crystal Reports)

I am using Visual Studio .NET 2003 and the version of Crystal that comes with it. I have a problem that when a report is displayed and it doesn't have any data I receive the following error message "Failed to open a rowset" and only a blank screen is shown. I need at least the Header labels to show. How can I make it show at least the header labels.Ok, I figured it out. The stored procedure that runs the report gathers data into a temp table. If the there isn't any data it made the report error out. I rewrote the SP to not use a temp table and all is working well.|||hey,
i am having the same problem with Crystal reports.
i have a few Crystal Reports that were being used as stand-alone reports.
Now i would like to develop an application (WITHOUT including the reports into the application) that shows all the reports that are in a specific directory (done!), list all the report names in a listview (done), and when the user clicks on a reportname.... i want the application to open the report.

Currently i have the following code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
'Dim report As ReportDocument = New ReportDocument
Dim ReportName As String
ReportName = Me.ListView1.SelectedItems(0).Text
report.Load(ConfigurationSettings.AppSettings("DirPath") & ReportName & ".rpt")
report.SetDatabaseLogon("testLogin", "testPassword")

Dim frm As New frmViewer
frm.Show()
End Sub

I rewrote the SP to not use a temp table and all is working well
how exactly was this done?

Thanks..... i hope you solve my problem!

Failed to open a fastload rowset

Hi.

In OLEDB Destination, AccessMode as OpenRowset Using FastLoad raises the following error:

"Failed to open a fastload rowset db_object. Check that the object exists in the database"

db_object exists in the database. Is there any key point for the table or view used for fastload?

Thanks

How did you set db_object to the OLE DB Destination component? Did you choose it from the drop-down list or typed it in the property grid? Does it have the schema part which is not included?

Thanks.

|||

Yes, I select the db_object from drop down list and also schema part is included.

Thanks

|||

Could you copy the entire error message here? Do you see that message at the design time or when you run the package?

Thanks.

|||

Following is the error raised at design time:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Model [OLE DB Destination [49]]: An OLE DB error has occurred. Error code: 0x80040E21.

Error at Model [OLE DB Destination [49]]: Failed to open a fastload rowset for ""PRATI"."CONFIG_MODEL"". Check that the object exists in the database.

Error at Model [DTS.Pipeline]: "component "OLE DB Destination" (49)" failed validation and returned validation status "VS_ISBROKEN".

Error at Model [DTS.Pipeline]: One or more component failed validation.

Error at Model: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

|||

Are you connecting to the SQL Server database? The fast load interface can be used only for SQL Server.

Thanks.

Friday, February 17, 2012

Extract values from XML column (rows) to a rowset

Hi,
I have a question regarding extracting XML data as relational data. From
the looks of it looks like the OPENXML statement is not relevant since the
data I need to extract resides in XML columns (in a table).
So I have this query
SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
MyXmlCol â' is an XML column, and the WHERE clause assures me that I know how
to access this XML data (using xpath)
Letâ's say the data in XML column looks like this
<MyStuff>
<Product>Windows Vista</Product>
<Version>1</Version>
</MyStuff>
(were in each row the value of the Product & Version nodes are different).
I want a table/rowset with two columns Product (xpath
/MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
I want the rowset/table to look like this:
Product Version
-- --
WindowsVista 1
WindowsHome 2
WindowsXP 3
How do I accomplish this ?
--
Asher.Asher
Take a look at this example to resturn Cities
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
</AddressType> ');
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="222 Main"
City="SanDiego"
State="CA"
ZipCode = "2222" />
</AddressType> ');
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
'varchar(50)' )
FROM @.tAddress
"Asher F." <asherfoa@.community.nospam> wrote in message
news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> Hi,
> I have a question regarding extracting XML data as relational data. From
> the looks of it looks like the OPENXML statement is not relevant since the
> data I need to extract resides in XML columns (in a table).
> So I have this query
> SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> MyXmlCol ? is an XML column, and the WHERE clause assures me that I know
> how
> to access this XML data (using xpath)
> Let?s say the data in XML column looks like this
> <MyStuff>
> <Product>Windows Vista</Product>
> <Version>1</Version>
> </MyStuff>
> (were in each row the value of the Product & Version nodes are different).
> I want a table/rowset with two columns Product (xpath
> /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> I want the rowset/table to look like this:
> Product Version
> -- --
> WindowsVista 1
> WindowsHome 2
> WindowsXP 3
> How do I accomplish this ?
>
> --
> Asher.|||Hello Asher,
I understand that you'd like to insert a table from a xml type value. You
may want to use openxml to do the job:
CREATE TABLE MyStuff( product varchar(20), version int)
go
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'
<MyStuff>
<Product>Windows Vista</Product>
<Version>1</Version>
</MyStuff>
'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Mystuff
SELECT *
FROM OPENXML(@.docHandle, N'/MyStuff', 2)
WITH (proudct varchar(20) '/MyStuff/Product',
version int '/MyStuff/Version')
EXEC sp_xml_removedocument @.docHandle
select * from mystuff
If you have any comments or feedback, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Uri,
Thanks for the quick response, I understand the code you suggested and it
works for me 80% of times. I would very much appreciate your help in this
follow-up.
To extend your example, letâ's say I have this:
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
<AddressRecord StreetLine1="222 Left"
City="New York"
State="NY"
ZipCode = "2222" />
</AddressType> ');
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="222 Main"
City="SanDiego"
State="CA"
ZipCode = "2222" />
</AddressType> ');
Note that the XML in the first row has two AddressRecord elements.
How do I make my result return a rowset with 3 rows representing all the
values:
SanDiego
New York
Houston
Thanks in advance!
--
Asher.
"Uri Dimant" wrote:
> Asher
> Take a look at this example to resturn Cities
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
> 'varchar(50)' )
> FROM @.tAddress
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> > Hi,
> > I have a question regarding extracting XML data as relational data. From
> > the looks of it looks like the OPENXML statement is not relevant since the
> > data I need to extract resides in XML columns (in a table).
> > So I have this query
> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> > MyXmlCol â' is an XML column, and the WHERE clause assures me that I know
> > how
> > to access this XML data (using xpath)
> > Letâ's say the data in XML column looks like this
> > <MyStuff>
> > <Product>Windows Vista</Product>
> > <Version>1</Version>
> > </MyStuff>
> > (were in each row the value of the Product & Version nodes are different).
> > I want a table/rowset with two columns Product (xpath
> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> > I want the rowset/table to look like this:
> > Product Version
> > -- --
> > WindowsVista 1
> > WindowsHome 2
> > WindowsXP 3
> >
> > How do I accomplish this ?
> >
> >
> >
> > --
> > Asher.
>
>|||Hello Peter,
Thank you for your response.
What I am trying to do is to extract data from an XML column I have in one
of my tables. I am trying to make the data in the XML column appear as part
of the normal rowset.
I will run a SQL SELECT statement from my C# code expecting to receive the
data back in the form of a rowset (so I can read it as primitive types using
IDataReader).
Anyway, Uri's response works for me in most of the cases, it doesn't cover
(and I tried several things myself) the case where I need to extract multiple
values (i.e. rows) from a single XML document (stored in the xml column in a
given row in my table).
Thanks for the help
--
Asher.
"Peter Yang [MSFT]" wrote:
> Hello Asher,
> I understand that you'd like to insert a table from a xml type value. You
> may want to use openxml to do the job:
> CREATE TABLE MyStuff( product varchar(20), version int)
> go
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'
> <MyStuff>
> <Product>Windows Vista</Product>
> <Version>1</Version>
> </MyStuff>
> '
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- Use OPENXML to provide rowset consisting of customer data.
> INSERT Mystuff
> SELECT *
> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
> WITH (proudct varchar(20) '/MyStuff/Product',
> version int '/MyStuff/Version')
> EXEC sp_xml_removedocument @.docHandle
> select * from mystuff
>
> If you have any comments or feedback, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Asher
I see what you mean
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
<AddressRecord StreetLine1="111 Left"
City="New_York"
State="NY"
ZipCode = "3333" />
</AddressType> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
WITH (City varchar(10))
"Asher F." <asherfoa@.community.nospam> wrote in message
news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> Hi Uri,
> Thanks for the quick response, I understand the code you suggested and it
> works for me 80% of times. I would very much appreciate your help in this
> follow-up.
> To extend your example, let?s say I have this:
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="222 Left"
> City="New York"
> State="NY"
> ZipCode = "2222" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> Note that the XML in the first row has two AddressRecord elements.
> How do I make my result return a rowset with 3 rows representing all the
> values:
> SanDiego
> New York
> Houston
> Thanks in advance!
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data.
>> > From
>> > the looks of it looks like the OPENXML statement is not relevant since
>> > the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> > know
>> > how
>> > to access this XML data (using xpath)
>> > Let?s say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are
>> > different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>|||Or
SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(50)')
FROM @.tAddress
UNION ALL
SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(50)')
FROM @.tAddress
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
> Asher
> I see what you mean
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="111 Left"
> City="New_York"
> State="NY"
> ZipCode = "3333" />
> </AddressType> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
> WITH (City varchar(10))
>
>
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
>> Hi Uri,
>> Thanks for the quick response, I understand the code you suggested and it
>> works for me 80% of times. I would very much appreciate your help in this
>> follow-up.
>> To extend your example, let?s say I have this:
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> <AddressRecord StreetLine1="222 Left"
>> City="New York"
>> State="NY"
>> ZipCode = "2222" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> Note that the XML in the first row has two AddressRecord elements.
>> How do I make my result return a rowset with 3 rows representing all the
>> values:
>> SanDiego
>> New York
>> Houston
>> Thanks in advance!
>> --
>> Asher.
>>
>> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data.
>> > From
>> > the looks of it looks like the OPENXML statement is not relevant since
>> > the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> > know
>> > how
>> > to access this XML data (using xpath)
>> > Let?s say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are
>> > different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>
>|||Hi Uri,
The OPENXML is probably not the solution since I have the XML document
stored in a column.
As for your second solution, it looks like it is in the right direction,
however I don't see how I can scale it to the case I have N (or an unkown
number of) AddressRecord nodes in each XML document (i.e. one row's XML
column might contain zero AddressRecord elements and another row might
contain 10...)
How do I scale this solution ?
--
Asher.
"Uri Dimant" wrote:
> Or
> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]', 'varchar(50)')
> FROM @.tAddress
> UNION ALL
> SELECT address.value('(/AddressType/AddressRecord/@.City)[2]', 'varchar(50)')
> FROM @.tAddress
>
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
> > Asher
> > I see what you mean
> >
> > DECLARE @.idoc int
> >
> > DECLARE @.doc varchar(1000)
> >
> > SET @.doc ='
> >
> > <AddressType name="Shipping">
> >
> > <AddressRecord StreetLine1="111 Main"
> >
> > City="Houston"
> >
> > State="TX"
> >
> > ZipCode = "11111" />
> >
> > <AddressRecord StreetLine1="111 Left"
> >
> > City="New_York"
> >
> > State="NY"
> >
> > ZipCode = "3333" />
> >
> > </AddressType> '
> >
> > EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> >
> > SELECT *
> >
> > FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
> >
> > WITH (City varchar(10))
> >
> >
> >
> >
> >
> >
> >
> > "Asher F." <asherfoa@.community.nospam> wrote in message
> > news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> >> Hi Uri,
> >> Thanks for the quick response, I understand the code you suggested and it
> >> works for me 80% of times. I would very much appreciate your help in this
> >> follow-up.
> >>
> >> To extend your example, letâ's say I have this:
> >>
> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="111 Main"
> >>
> >> City="Houston"
> >>
> >> State="TX"
> >>
> >> ZipCode = "11111" />
> >> <AddressRecord StreetLine1="222 Left"
> >>
> >> City="New York"
> >>
> >> State="NY"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="222 Main"
> >>
> >> City="SanDiego"
> >>
> >> State="CA"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> Note that the XML in the first row has two AddressRecord elements.
> >> How do I make my result return a rowset with 3 rows representing all the
> >> values:
> >> SanDiego
> >> New York
> >> Houston
> >>
> >> Thanks in advance!
> >> --
> >> Asher.
> >>
> >>
> >> "Uri Dimant" wrote:
> >>
> >> Asher
> >> Take a look at this example to resturn Cities
> >>
> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="111 Main"
> >>
> >> City="Houston"
> >>
> >> State="TX"
> >>
> >> ZipCode = "11111" />
> >>
> >> </AddressType> ');
> >>
> >> INSERT INTO @.tAddress (Address)
> >>
> >> VALUES ('<AddressType name="Shipping">
> >>
> >> <AddressRecord StreetLine1="222 Main"
> >>
> >> City="SanDiego"
> >>
> >> State="CA"
> >>
> >> ZipCode = "2222" />
> >>
> >> </AddressType> ');
> >>
> >> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
> >> 'varchar(50)' )
> >>
> >> FROM @.tAddress
> >>
> >>
> >>
> >> "Asher F." <asherfoa@.community.nospam> wrote in message
> >> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
> >> > Hi,
> >> > I have a question regarding extracting XML data as relational data.
> >> > From
> >> > the looks of it looks like the OPENXML statement is not relevant since
> >> > the
> >> > data I need to extract resides in XML columns (in a table).
> >> > So I have this query
> >> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
> >> > MyXmlCol â' is an XML column, and the WHERE clause assures me that I
> >> > know
> >> > how
> >> > to access this XML data (using xpath)
> >> > Letâ's say the data in XML column looks like this
> >> > <MyStuff>
> >> > <Product>Windows Vista</Product>
> >> > <Version>1</Version>
> >> > </MyStuff>
> >> > (were in each row the value of the Product & Version nodes are
> >> > different).
> >> > I want a table/rowset with two columns Product (xpath
> >> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
> >> > I want the rowset/table to look like this:
> >> > Product Version
> >> > -- --
> >> > WindowsVista 1
> >> > WindowsHome 2
> >> > WindowsXP 3
> >> >
> >> > How do I accomplish this ?
> >> >
> >> >
> >> >
> >> > --
> >> > Asher.
> >>
> >>
> >>
> >
> >
>
>|||Check out the nodes method. It returns a table with one column of datatype XML. So if the XML
instance is a column inside a table, you use this with APPLY against the table where the column
exists. Example, assuming table is temp table instead of table variable:
SELECT nc.value('@.City[1]', 'varchar(39)')
FROM #tAddress
CROSS APPLY Address.nodes('/AddressType/AddressRecord') AS nt(nc)
---
Houston
New York
SanDiego
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Asher F." <asherfoa@.community.nospam> wrote in message
news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
> Hi Uri,
> Thanks for the quick response, I understand the code you suggested and it
> works for me 80% of times. I would very much appreciate your help in this
> follow-up.
> To extend your example, let's say I have this:
> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="111 Main"
> City="Houston"
> State="TX"
> ZipCode = "11111" />
> <AddressRecord StreetLine1="222 Left"
> City="New York"
> State="NY"
> ZipCode = "2222" />
> </AddressType> ');
> INSERT INTO @.tAddress (Address)
> VALUES ('<AddressType name="Shipping">
> <AddressRecord StreetLine1="222 Main"
> City="SanDiego"
> State="CA"
> ZipCode = "2222" />
> </AddressType> ');
> Note that the XML in the first row has two AddressRecord elements.
> How do I make my result return a rowset with 3 rows representing all the
> values:
> SanDiego
> New York
> Houston
> Thanks in advance!
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Asher
>> Take a look at this example to resturn Cities
>> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="111 Main"
>> City="Houston"
>> State="TX"
>> ZipCode = "11111" />
>> </AddressType> ');
>> INSERT INTO @.tAddress (Address)
>> VALUES ('<AddressType name="Shipping">
>> <AddressRecord StreetLine1="222 Main"
>> City="SanDiego"
>> State="CA"
>> ZipCode = "2222" />
>> </AddressType> ');
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)' )
>> FROM @.tAddress
>>
>> "Asher F." <asherfoa@.community.nospam> wrote in message
>> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> > Hi,
>> > I have a question regarding extracting XML data as relational data. From
>> > the looks of it looks like the OPENXML statement is not relevant since the
>> > data I need to extract resides in XML columns (in a table).
>> > So I have this query
>> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> > MyXmlCol - is an XML column, and the WHERE clause assures me that I know
>> > how
>> > to access this XML data (using xpath)
>> > Let's say the data in XML column looks like this
>> > <MyStuff>
>> > <Product>Windows Vista</Product>
>> > <Version>1</Version>
>> > </MyStuff>
>> > (were in each row the value of the Product & Version nodes are different).
>> > I want a table/rowset with two columns Product (xpath
>> > /MyStuff/Product/text()) and Version (xpath /MyStuff/Version/text())
>> > I want the rowset/table to look like this:
>> > Product Version
>> > -- --
>> > WindowsVista 1
>> > WindowsHome 2
>> > WindowsXP 3
>> >
>> > How do I accomplish this ?
>> >
>> >
>> >
>> > --
>> > Asher.
>>|||See Tibor's reply , it is what you wanted
"Asher F." <asherfoa@.community.nospam> wrote in message
news:38DFF454-06D5-4E00-B623-F5FB216D87EC@.microsoft.com...
> Hi Uri,
> The OPENXML is probably not the solution since I have the XML document
> stored in a column.
> As for your second solution, it looks like it is in the right direction,
> however I don't see how I can scale it to the case I have N (or an unkown
> number of) AddressRecord nodes in each XML document (i.e. one row's XML
> column might contain zero AddressRecord elements and another row might
> contain 10...)
> How do I scale this solution ?
> --
> Asher.
>
> "Uri Dimant" wrote:
>> Or
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> 'varchar(50)')
>> FROM @.tAddress
>> UNION ALL
>> SELECT address.value('(/AddressType/AddressRecord/@.City)[2]',
>> 'varchar(50)')
>> FROM @.tAddress
>>
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:O9Qv3C1THHA.5108@.TK2MSFTNGP06.phx.gbl...
>> > Asher
>> > I see what you mean
>> >
>> > DECLARE @.idoc int
>> >
>> > DECLARE @.doc varchar(1000)
>> >
>> > SET @.doc ='
>> >
>> > <AddressType name="Shipping">
>> >
>> > <AddressRecord StreetLine1="111 Main"
>> >
>> > City="Houston"
>> >
>> > State="TX"
>> >
>> > ZipCode = "11111" />
>> >
>> > <AddressRecord StreetLine1="111 Left"
>> >
>> > City="New_York"
>> >
>> > State="NY"
>> >
>> > ZipCode = "3333" />
>> >
>> > </AddressType> '
>> >
>> > EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
>> >
>> > SELECT *
>> >
>> > FROM OPENXML (@.idoc, '/AddressType/AddressRecord',1)
>> >
>> > WITH (City varchar(10))
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Asher F." <asherfoa@.community.nospam> wrote in message
>> > news:F678FA42-605F-4425-BED3-EBEF75A47219@.microsoft.com...
>> >> Hi Uri,
>> >> Thanks for the quick response, I understand the code you suggested and
>> >> it
>> >> works for me 80% of times. I would very much appreciate your help in
>> >> this
>> >> follow-up.
>> >>
>> >> To extend your example, let?s say I have this:
>> >>
>> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="111 Main"
>> >>
>> >> City="Houston"
>> >>
>> >> State="TX"
>> >>
>> >> ZipCode = "11111" />
>> >> <AddressRecord StreetLine1="222 Left"
>> >>
>> >> City="New York"
>> >>
>> >> State="NY"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="222 Main"
>> >>
>> >> City="SanDiego"
>> >>
>> >> State="CA"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> Note that the XML in the first row has two AddressRecord elements.
>> >> How do I make my result return a rowset with 3 rows representing all
>> >> the
>> >> values:
>> >> SanDiego
>> >> New York
>> >> Houston
>> >>
>> >> Thanks in advance!
>> >> --
>> >> Asher.
>> >>
>> >>
>> >> "Uri Dimant" wrote:
>> >>
>> >> Asher
>> >> Take a look at this example to resturn Cities
>> >>
>> >> declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="111 Main"
>> >>
>> >> City="Houston"
>> >>
>> >> State="TX"
>> >>
>> >> ZipCode = "11111" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> INSERT INTO @.tAddress (Address)
>> >>
>> >> VALUES ('<AddressType name="Shipping">
>> >>
>> >> <AddressRecord StreetLine1="222 Main"
>> >>
>> >> City="SanDiego"
>> >>
>> >> State="CA"
>> >>
>> >> ZipCode = "2222" />
>> >>
>> >> </AddressType> ');
>> >>
>> >> SELECT address.value('(/AddressType/AddressRecord/@.City)[1]',
>> >> 'varchar(50)' )
>> >>
>> >> FROM @.tAddress
>> >>
>> >>
>> >>
>> >> "Asher F." <asherfoa@.community.nospam> wrote in message
>> >> news:407A573D-01B4-4AEE-A10B-FA2CB5AA0A17@.microsoft.com...
>> >> > Hi,
>> >> > I have a question regarding extracting XML data as relational data.
>> >> > From
>> >> > the looks of it looks like the OPENXML statement is not relevant
>> >> > since
>> >> > the
>> >> > data I need to extract resides in XML columns (in a table).
>> >> > So I have this query
>> >> > SELECT [MyXmlCol] FROM [MyTable] WHERE [XmlTypeName]='Schema1'
>> >> > MyXmlCol ? is an XML column, and the WHERE clause assures me that I
>> >> > know
>> >> > how
>> >> > to access this XML data (using xpath)
>> >> > Let?s say the data in XML column looks like this
>> >> > <MyStuff>
>> >> > <Product>Windows Vista</Product>
>> >> > <Version>1</Version>
>> >> > </MyStuff>
>> >> > (were in each row the value of the Product & Version nodes are
>> >> > different).
>> >> > I want a table/rowset with two columns Product (xpath
>> >> > /MyStuff/Product/text()) and Version (xpath
>> >> > /MyStuff/Version/text())
>> >> > I want the rowset/table to look like this:
>> >> > Product Version
>> >> > -- --
>> >> > WindowsVista 1
>> >> > WindowsHome 2
>> >> > WindowsXP 3
>> >> >
>> >> > How do I accomplish this ?
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Asher.
>> >>
>> >>
>> >>
>> >
>> >
>>|||Did you look at my suggestion? It produces exactly the result you asked for...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Asher F." <asherfoa@.community.nospam> wrote in message
news:161F4EC4-7C19-430D-91EF-D2684D840D9D@.microsoft.com...
> Hello Peter,
> Thank you for your response.
> What I am trying to do is to extract data from an XML column I have in one
> of my tables. I am trying to make the data in the XML column appear as part
> of the normal rowset.
> I will run a SQL SELECT statement from my C# code expecting to receive the
> data back in the form of a rowset (so I can read it as primitive types using
> IDataReader).
> Anyway, Uri's response works for me in most of the cases, it doesn't cover
> (and I tried several things myself) the case where I need to extract multiple
> values (i.e. rows) from a single XML document (stored in the xml column in a
> given row in my table).
> Thanks for the help
> --
> Asher.
>
> "Peter Yang [MSFT]" wrote:
>> Hello Asher,
>> I understand that you'd like to insert a table from a xml type value. You
>> may want to use openxml to do the job:
>> CREATE TABLE MyStuff( product varchar(20), version int)
>> go
>> DECLARE @.docHandle int
>> declare @.xmlDocument xml
>> set @.xmlDocument = N'
>> <MyStuff>
>> <Product>Windows Vista</Product>
>> <Version>1</Version>
>> </MyStuff>
>> '
>> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
>> -- Use OPENXML to provide rowset consisting of customer data.
>> INSERT Mystuff
>> SELECT *
>> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
>> WITH (proudct varchar(20) '/MyStuff/Product',
>> version int '/MyStuff/Version')
>> EXEC sp_xml_removedocument @.docHandle
>> select * from mystuff
>>
>> If you have any comments or feedback, please feel free to let's know. Thank
>> you.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications
>> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>|||Thank you Tibor, it looks like exactlly what I need.
I am now reading the relevant documentation about the functions you used so
I can get a deeper understanding.
Thank you very much for your help!
--
Asher.
"Tibor Karaszi" wrote:
> Did you look at my suggestion? It produces exactly the result you asked for...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Asher F." <asherfoa@.community.nospam> wrote in message
> news:161F4EC4-7C19-430D-91EF-D2684D840D9D@.microsoft.com...
> > Hello Peter,
> > Thank you for your response.
> > What I am trying to do is to extract data from an XML column I have in one
> > of my tables. I am trying to make the data in the XML column appear as part
> > of the normal rowset.
> > I will run a SQL SELECT statement from my C# code expecting to receive the
> > data back in the form of a rowset (so I can read it as primitive types using
> > IDataReader).
> > Anyway, Uri's response works for me in most of the cases, it doesn't cover
> > (and I tried several things myself) the case where I need to extract multiple
> > values (i.e. rows) from a single XML document (stored in the xml column in a
> > given row in my table).
> >
> > Thanks for the help
> >
> > --
> > Asher.
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> >> Hello Asher,
> >>
> >> I understand that you'd like to insert a table from a xml type value. You
> >> may want to use openxml to do the job:
> >>
> >> CREATE TABLE MyStuff( product varchar(20), version int)
> >> go
> >>
> >> DECLARE @.docHandle int
> >> declare @.xmlDocument xml
> >> set @.xmlDocument = N'
> >> <MyStuff>
> >> <Product>Windows Vista</Product>
> >> <Version>1</Version>
> >> </MyStuff>
> >> '
> >> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> >> -- Use OPENXML to provide rowset consisting of customer data.
> >> INSERT Mystuff
> >> SELECT *
> >> FROM OPENXML(@.docHandle, N'/MyStuff', 2)
> >> WITH (proudct varchar(20) '/MyStuff/Product',
> >> version int '/MyStuff/Version')
> >>
> >> EXEC sp_xml_removedocument @.docHandle
> >>
> >> select * from mystuff
> >>
> >>
> >> If you have any comments or feedback, please feel free to let's know. Thank
> >> you.
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Community Support
> >> ==================================================> >> Get notification to my posts through email? Please refer to
> >> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> >> ications
> >> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> >> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> >> where an initial response from the community or a Microsoft Support
> >> Engineer within 1 business day is acceptable. Please note that each follow
> >> up response may take approximately 2 business days as the support
> >> professional working with you may need further investigation to reach the
> >> most efficient resolution. The offering is not appropriate for situations
> >> that require urgent, real-time or phone-based interactions or complex
> >> project analysis and dump analysis issues. Issues of this nature are best
> >> handled working with a dedicated Microsoft Support Engineer by contacting
> >> Microsoft Customer Support Services (CSS) at
> >> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
>

Thursday, March 29, 2012

Failed to Open Rowset Error in Crystal Reports

failed to open rowset

Failed to open a rowset Warning/Error

Failed to open a rowset (Crystal Reports)

Failed to open a fastload rowset

Friday, February 17, 2012

Extract values from XML column (rows) to a rowset

Extract values from XML column (rows) to a rowset

Extract values from XML column (rows) to a rowset

Extracting Sql

Blog Archive

About Me