Home All Groups Group Topic Archive Search About

Crazy Row Numbering Poblem

microsoft.public.sqlserver.programming
Author
19 May 2006 12:27 PM
rhaazy
I'm using ms sql 2000.

First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
  <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
  <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
  <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
  <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
  <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
  <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
  <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
  <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
  <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
  <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
  <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
  </scanattributes>
  </scan>

Here is what I have so far:
declare @iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

*/
    INSERT INTO #temp
    SELECT *  FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
            WITH(
                ID nvarchar(50) './@ID',
                ParentID nvarchar(50) './@ParentID',
                Name nvarchar(50) './@Name',
                scanattribute nvarchar(50) '.'
                )

        INSERT INTO #dup
    SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
    WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'

    INSERT INTO tblTest3(instance, attid, sectionid, name)
    SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
    AND    #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
    FROM tblScanAttribute, #temp
    WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'

The Results are as follows for tblTest3:
    2    151    18    5/4/2006 1:11 PM
    9    151    18    5/4/2006 12:57 PM
    10    151    18    5/4/2006 12:59 PM
    1    151    18    5/4/2006 1:04 PM
    6    151    18    5/4/2006 12:06 PM
    7    151    18    5/4/2006 12:15 PM
    8    151    18    5/4/2006 12:36 PM
    3    151    18    5/4/2006 11:42 AM
    4    151    18    5/4/2006 11:48 AM
    5    151    18    5/4/2006 12:05 PM
    5    152    18    Installed ClientScanServiceSetup
    10    152    18    Removed ClientScanServiceSetup
    5    152    18    Installed ClientScanServiceSetup
    10    152    18    Removed ClientScanServiceSetup
    5    152    18    Installed ClientScanServiceSetup
    10    152    18    Removed ClientScanServiceSetup
    5    152    18    Installed ClientScanServiceSetup
    10    152    18    Removed ClientScanServiceSetup
    5    152    18    Installed ClientScanServiceSetup
    10    152    18    Removed ClientScanServiceSetup

As you can see I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
    2    151    18    5/4/2006 1:11 PM
    9    151    18    5/4/2006 12:57 PM
    10    151    18    5/4/2006 12:59 PM
    1    151    18    5/4/2006 1:04 PM
    6    151    18    5/4/2006 12:06 PM
    7    151    18    5/4/2006 12:15 PM
    8    151    18    5/4/2006 12:36 PM
    3    151    18    5/4/2006 11:42 AM
    4    151    18    5/4/2006 11:48 AM
    5    151    18    5/4/2006 12:05 PM
    1    152    18    Installed ClientScanServiceSetup
    10    152    18    Removed ClientScanServiceSetup
    9    152    18    Installed ClientScanServiceSetup
    2    152    18    Removed ClientScanServiceSetup
    8    152    18    Installed ClientScanServiceSetup
    3    152    18    Removed ClientScanServiceSetup
    5    152    18    Installed ClientScanServiceSetup
    7    152    18    Removed ClientScanServiceSetup
    6    152    18    Installed ClientScanServiceSetup
    4    152    18    Removed ClientScanServiceSetup

IF anyone can help me out with this I would appreciate it greatly.

Author
19 May 2006 12:34 PM
rhaazy
I forgot to include the other tbl where I get the attid from. so here
it is.
    151    18    Creation Time    ND        0    5/9/2006 1:56:00 PM    5/9/2006 1:56:00 PM    0
    152    18    Description    ND        0    5/9/2006 1:56:00 PM    5/9/2006 1:56:00 PM    0
Author
19 May 2006 1:22 PM
Alejandro Mesa
rhaazy,

we need another column to break the ties. Try adding an identity column to
the temporary table #temp.

create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique    -- new column
)

INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT *  FROM openxml(...
....

INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
    instance = (
    select count(*)
    from #dup
    where
        #dup.attid = tblScanAttribute.ScanAttributeID
        AND
        (
        #dup.[name] <= #temp.scanattribute
        or
        (#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
        )
    ),
    tblScanAttribute.ScanAttributeID,
    tblScanAttribute.ScanSectionID,
    #temp.scanattribute
FROM
    tblScanAttribute, #temp
WHERE
    #temp.ID like '18.%'
    AND tblScanAttribute.Name = #temp.Name
    AND tblScanAttribute.ScanSectionID like '18'


AMB

Show quote
"rhaazy" wrote:

> I forgot to include the other tbl where I get the attid from. so here
> it is.
>     151    18    Creation Time    ND        0    5/9/2006 1:56:00 PM    5/9/2006 1:56:00 PM    0
>     152    18    Description    ND        0    5/9/2006 1:56:00 PM    5/9/2006 1:56:00 PM    0
>
>
Author
19 May 2006 1:44 PM
rhaazy
NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
        instance = (
        select count(*)
        from #dup
        where
                #dup.attid = tblScanAttribute.ScanAttributeID
                AND
                (
                #dup.[name] <= #temp.scanattribute
                or
                (#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
                )
        ),

Does this imply I also need to add the pk column to the #dup table???
Author
19 May 2006 1:55 PM
Alejandro Mesa
rhaazy,

Sure, but here it will not have identity property. I will be populated from
#temp.


AMB

Show quote
"rhaazy" wrote:

> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
>         instance = (
>         select count(*)
>         from #dup
>         where
>                 #dup.attid = tblScanAttribute.ScanAttributeID
>                 AND
>                 (
>                 #dup.[name] <= #temp.scanattribute
>                 or
>                 (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
>                 )
>         ),
>
> Does this imply I also need to add the pk column to the #dup table???
>
>
Author
19 May 2006 1:12 PM
Steve Kass
It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
....

If you have more than one year, it will get worse.

Steve Kass
Drew University

rhaazy wrote:

Show quote
>I'm using ms sql 2000.
>
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
>  <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
>  <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
>  <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
>  <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
>  <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
>  <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
>  <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
>  <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
>  <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
>  <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
>  <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
>  </scanattributes>
>  </scan>
>
>Here is what I have so far:
>declare @iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>
>EXEC sp_xml_preparedocument @iTree OUTPUT, @doc
>
>*/
>    INSERT INTO #temp
>    SELECT *  FROM openxml(@iTree,
>'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
>            WITH(
>                ID nvarchar(50) './@ID',
>                ParentID nvarchar(50) './@ParentID',
>                Name nvarchar(50) './@Name',
>                scanattribute nvarchar(50) '.'
>                )
>
>        INSERT INTO #dup
>    SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
>tblScanAttribute, #temp
>    WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>
>    INSERT INTO tblTest3(instance, attid, sectionid, name)
>    SELECT instance = (select count(*) from #dup where #dup.attid =
>tblScanAttribute.ScanAttributeID
>    AND    #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
>tblScanAttribute.ScanSectionID, #temp.scanattribute
>    FROM tblScanAttribute, #temp
>    WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>
>The Results are as follows for tblTest3:
>    2    151    18    5/4/2006 1:11 PM
>    9    151    18    5/4/2006 12:57 PM
>    10    151    18    5/4/2006 12:59 PM
>    1    151    18    5/4/2006 1:04 PM
>    6    151    18    5/4/2006 12:06 PM
>    7    151    18    5/4/2006 12:15 PM
>    8    151    18    5/4/2006 12:36 PM
>    3    151    18    5/4/2006 11:42 AM
>    4    151    18    5/4/2006 11:48 AM
>    5    151    18    5/4/2006 12:05 PM
>    5    152    18    Installed ClientScanServiceSetup
>    10    152    18    Removed ClientScanServiceSetup
>    5    152    18    Installed ClientScanServiceSetup
>    10    152    18    Removed ClientScanServiceSetup
>    5    152    18    Installed ClientScanServiceSetup
>    10    152    18    Removed ClientScanServiceSetup
>    5    152    18    Installed ClientScanServiceSetup
>    10    152    18    Removed ClientScanServiceSetup
>    5    152    18    Installed ClientScanServiceSetup
>    10    152    18    Removed ClientScanServiceSetup
>
>As you can see I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
>    2    151    18    5/4/2006 1:11 PM
>    9    151    18    5/4/2006 12:57 PM
>    10    151    18    5/4/2006 12:59 PM
>    1    151    18    5/4/2006 1:04 PM
>    6    151    18    5/4/2006 12:06 PM
>    7    151    18    5/4/2006 12:15 PM
>    8    151    18    5/4/2006 12:36 PM
>    3    151    18    5/4/2006 11:42 AM
>    4    151    18    5/4/2006 11:48 AM
>    5    151    18    5/4/2006 12:05 PM
>    1    152    18    Installed ClientScanServiceSetup
>    10    152    18    Removed ClientScanServiceSetup
>    9    152    18    Installed ClientScanServiceSetup
>    2    152    18    Removed ClientScanServiceSetup
>    8    152    18    Installed ClientScanServiceSetup
>    3    152    18    Removed ClientScanServiceSetup
>    5    152    18    Installed ClientScanServiceSetup
>    7    152    18    Removed ClientScanServiceSetup
>    6    152    18    Installed ClientScanServiceSetup
>    4    152    18    Removed ClientScanServiceSetup
>
>IF anyone can help me out with this I would appreciate it greatly.
>
> 
>
Author
19 May 2006 1:45 PM
markc600
You should be able to use your ID column to resolve duplicates.
Try changing

        INSERT INTO tblTest3(instance, attid, sectionid, name)
        SELECT instance = (select count(*) from #dup where #dup.attid =

tblScanAttribute.ScanAttributeID
        AND     #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
        FROM tblScanAttribute, #temp
        WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'



to


        INSERT INTO tblTest3(instance, attid, sectionid, name)
        SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
                           AND     ((#dup.name<#temp.scanattribute)
                               or   (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
               tblScanAttribute.ScanAttributeID,
               tblScanAttribute.ScanSectionID,
               #temp.scanattribute
        FROM tblScanAttribute, #temp
        WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'
Author
19 May 2006 1:55 PM
rhaazy
I don't know who you are or where you come from but you are the
greatest person who ever lived....  I have spent all week trying to
figure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right.  I am an intern and I've only been
database programming for 2 weeks and was pretty pleased I got as far as
I did.  I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.

Thanks again.
Author
19 May 2006 2:30 PM
Alejandro Mesa
Good catch!!!

Show quote
"markc***@hotmail.com" wrote:

>
> You should be able to use your ID column to resolve duplicates.
> Try changing
>
>         INSERT INTO tblTest3(instance, attid, sectionid, name)
>         SELECT instance = (select count(*) from #dup where #dup.attid =
>
> tblScanAttribute.ScanAttributeID
>         AND     #dup.name<=#temp.scanattribute),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID, #temp.scanattribute
>         FROM tblScanAttribute, #temp
>         WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
>
>
> to
>
>
>         INSERT INTO tblTest3(instance, attid, sectionid, name)
>         SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
>                            AND     ((#dup.name<#temp.scanattribute)
>                                or   (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
>                tblScanAttribute.ScanAttributeID,
>                tblScanAttribute.ScanSectionID,
>                #temp.scanattribute
>         FROM tblScanAttribute, #temp
>         WHERE  #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
>

AddThis Social Bookmark Button

Post Other interesting topics
can't delete a row from primary table - is there an SP for this?
Ranking Rows
simple trigger question
UDFs - what resources used?
Problem with column prefix