getting a set of values from xml

i have imported xml into an xml datatype variable. here is a tiny version of my xml file.

<Root>

<TOP>

<USERS>

<USER>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>'

I need to pullout just the username, so the query method is out as it will return it in xml format. how do i just get a column containing all of the usernames?

You could use nodes table-value function:

create table #xml_table

(

xml_col xml

)

go

insert into #xml_table values('<Root>

<TOP>

<USERS>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>

')

select x.value('.[1]','varchar(100)')

from #xml_table t cross apply xml_col.nodes('/Root/TOP/USERS/USER/USERNAME/text()') as tab(x)

Getting a final version of a person into a DW

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first?

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

Fuzzy could be of help here. The fuzzy grouping can be used for deduplicating, the fuzzy lookup to check whether you already have a record that resembles your new one.

You will need to spend some time (by testing) to figure out a similarity value that suits your situation. In a DW environment, I think this should be a business decision.

From what I understand, ssno probably needs to be involved. In the lookup (haven't used the grouping yet) you can set similarity values for ssno addressno and name. For example, ssno (or birthdate?) needs a similarity of 1, and the name needs to have a similarity > 0.6

Regards,

Pipo

Getting 20533 Error as a result of subreports.

I am using VB6 with crystal reports version 8.5 (the OCX) and Access (Jet) database.

I have created a report which works fine on my dev machine and am trying to deploy to another machine.
When the report is run on the other machine an error 20533 Unable to open database error is received.
The report has an embedded subreport, which if I remove the subreport, works fine.
I have validated the database for both the main report and the subreport.

Here is the code I am using to run the report.

With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"

.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID") '{?OrderID}
.ReportTitle = gSystem.Company_Name
.Action = 1
End With

I'm at a loss... any help would be apprieciated. Thanks.Hi all... I figured out what the issue was. It was fixed with the following code that sets the database location for the subreport.
I have posted the code fix for anyone else that may run into this issue.

Thanks to all that took a look at this for me.

With MDIMain.CrystalReport
.ReportFileName = App.Path & "\reports\Summary.rpt"
.RetrieveDataFiles
Do Until .DataFiles(xCntr) = ""
.DataFiles(xCntr) = gSystem.DB_Path
xCntr = xCntr + 1
Loop
.SelectionFormula = "{OrderHeader.OrderID} =" & rsOrder("OrderID")
.ReportTitle = gSystem.Company_Name

.SubreportToChange = "Selected Options"
.DataFiles(0) = gSystem.DB_Path

.SubreportToChange = ""
.Action = 1
End With

Getting 17882 and 10055 errors

Hi all,
I'm getting 17882 and 10055 errors on one of my SQL 2000 EE Instances.
Using version 8.00.818. I've found a couple of Knowledge Base articles
(890200, 891311) about these errors.
How do I obtain the hotfixes for 8.00.990 and 8.00.997?
I'm uneasy about installing Service Pack 4 because of the problems I've read
about.
Thanks for your help.I am also having the same issue were you able to find the hotfix reference i
n
KB891311?
"tknoob" wrote:

> Hi all,
> I'm getting 17882 and 10055 errors on one of my SQL 2000 EE Instances.
> Using version 8.00.818. I've found a couple of Knowledge Base articles
> (890200, 891311) about these errors.
> How do I obtain the hotfixes for 8.00.990 and 8.00.997?
> I'm uneasy about installing Service Pack 4 because of the problems I've re
ad
> about.
> Thanks for your help.

Getting 17882 and 10055 errors

Hi all,
I'm getting 17882 and 10055 errors on one of my SQL 2000 EE Instances.
Using version 8.00.818. I've found a couple of Knowledge Base articles
(890200, 891311) about these errors.
How do I obtain the hotfixes for 8.00.990 and 8.00.997?
I'm uneasy about installing Service Pack 4 because of the problems I've read
about.
Thanks for your help.I am also having the same issue were you able to find the hotfix reference in
KB891311?
"tknoob" wrote:
> Hi all,
> I'm getting 17882 and 10055 errors on one of my SQL 2000 EE Instances.
> Using version 8.00.818. I've found a couple of Knowledge Base articles
> (890200, 891311) about these errors.
> How do I obtain the hotfixes for 8.00.990 and 8.00.997?
> I'm uneasy about installing Service Pack 4 because of the problems I've read
> about.
> Thanks for your help.

Get Version of Database

Hi,
Does a SQL Server database have a version number assoicated with it.
We are going to be continually updating a database and would like to
associate a database version with each update e.g (1, 1.1, 1.2...etc)
Is there a version property of the database I can use or will I have to
do it with a new table
Thanks
Jerry
New Table. :-)
HTH, Jens Suessmeyer.
|||You have to customize this using a table but there is no built in information.
"JeremiahOSullivan@.gmail.com" wrote:

> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>
|||Hi,
You have to create a new table and need to write a new stored procedure to
store the versions. ALl manual.
Thanks
Hari
SQL Server MVP
<JeremiahOSullivan@.gmail.com> wrote in message
news:1128502216.266668.98490@.g43g2000cwa.googlegro ups.com...
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>

Get Version of Database

Hi,
Does a SQL Server database have a version number assoicated with it.
We are going to be continually updating a database and would like to
associate a database version with each update e.g (1, 1.1, 1.2...etc)
Is there a version property of the database I can use or will I have to
do it with a new table
Thanks
JerryNew Table. :-)
HTH, Jens Suessmeyer.|||You have to customize this using a table but there is no built in information.
"JeremiahOSullivan@.gmail.com" wrote:
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>|||Hi,
You have to create a new table and need to write a new stored procedure to
store the versions. ALl manual.
Thanks
Hari
SQL Server MVP
<JeremiahOSullivan@.gmail.com> wrote in message
news:1128502216.266668.98490@.g43g2000cwa.googlegroups.com...
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>

Get Version of Database

Hi,
Does a SQL Server database have a version number assoicated with it.
We are going to be continually updating a database and would like to
associate a database version with each update e.g (1, 1.1, 1.2...etc)
Is there a version property of the database I can use or will I have to
do it with a new table
Thanks
JerryNew Table. :-)
HTH, Jens Suessmeyer.|||You have to customize this using a table but there is no built in informatio
n.
"JeremiahOSullivan@.gmail.com" wrote:

> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>|||Hi,
You have to create a new table and need to write a new stored procedure to
store the versions. ALl manual.
Thanks
Hari
SQL Server MVP
<JeremiahOSullivan@.gmail.com> wrote in message
news:1128502216.266668.98490@.g43g2000cwa.googlegroups.com...
> Hi,
> Does a SQL Server database have a version number assoicated with it.
> We are going to be continually updating a database and would like to
> associate a database version with each update e.g (1, 1.1, 1.2...etc)
> Is there a version property of the database I can use or will I have to
> do it with a new table
> Thanks
> Jerry
>