Extracting certain values from XML column

Question

i have the following sql query:

 select         
I.N.value('@DRN', 'varchar(50)') as DRN,
I.N.value('@CR_GUID', 'varchar(50)') AS CR,
I.N.value('@DR_GUID', 'varchar(50)') AS DR,
I.N.value('@AMOUNT', 'money') as Amount,
I.N.value('@DATE', 'Date') as ProDate
from Table as T
cross apply T.column.nodes('/ITEMS/ITEM') as I(N)

Currently it will bring back everything, example:

DRN    CR         DR        Amount        Prodate
12     2p1rf      684pb     4686.23       2012-11-14
12     586io      fdc25     4686.23       2012-11-16
13     hnv7p      19i6f     1800.00       2012-11-20
14     cd7k0      9s2vc      570.50       2012-11-20
15     pm78s      qw3d5     8500.00       2012-11-23

how do i exclude the following from the results, this is from the same row in the xml column?

 DRN    CR         DR        Amount        Prodate
*12*     2p1rf      684pb     4686.23       2012-11-14
*12*     586io      fdc25     4686.23       2012-11-16

i only want the query to bring back rows with one item in the xml column not mutliple values.

thank you

how do i then go about to bring back only the first item in a row with multiple items:

 DRN    CR         DR        Amount        Prodate
*12*     2p1rf      684pb     4686.23       2012-11-14
*12*     586io      fdc25     4686.23       2012-11-16

so i want to only see the item with the date 2012-11-14 not everything pertaining to the row? i hope that makes sense?

gbn · Accepted Answer · 2012-11-28 08:22:51Z

2

You should be able to do an inline aggregate and filter on that

SELECT
    X.DRN, X.CR, X.DR, X.Amount, X.ProDate
FROM
    (
    SELECT
        COUNT(*) OVER (PARTITION BY I.N.value('@DRN', 'varchar(50)')) AS CountPerDRN,
        I.N.value('@DRN', 'varchar(50)') as DRN,
        I.N.value('@CR_GUID', 'varchar(50)') AS CR,
        I.N.value('@DR_GUID', 'varchar(50)') AS DR,
        I.N.value('@AMOUNT', 'money') as Amount,
        I.N.value('@DATE', 'Date') as ProDate
    from
        MyTable as T
        cross apply
        T.Mycolumn.nodes('/ITEMS/ITEM') as I(N)
    ) X
WHERE
   X.CountPerDRN= 1

Edit, after question addition

Simply change the inline aggregate to a window function
In this case, I've chosen the row based on the lowest ProDate

SELECT
    X.DRN, X.CR, X.DR, X.Amount, X.ProDate
FROM
    (
    SELECT
        ROW_NUMBER() OVER (
                  PARTITION BY I.N.value('@DRN', 'varchar(50)')
                  ORDER BY I.N.value('@DATE', 'Date')
                  ) AS rnPerDRN,
        I.N.value('@DRN', 'varchar(50)') as DRN,
        I.N.value('@CR_GUID', 'varchar(50)') AS CR,
        I.N.value('@DR_GUID', 'varchar(50)') AS DR,
        I.N.value('@AMOUNT', 'money') as Amount,
        I.N.value('@DATE', 'Date') as ProDate
    from
        MyTable as T
        cross apply
        T.Mycolumn.nodes('/ITEMS/ITEM') as I(N)
    ) X
WHERE
   X.rnPerDRN = 1

edited Nov 28, 2012 at 8:22

answered Nov 28, 2012 at 8:03

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1854183 Over a year ago

i will i go about to bring back for example just the first item if the row contains more than one:

gbn Over a year ago

@user1854183: sure, also add sample data/XML please

user1854183 Over a year ago

hi i have added it to the original question, sorry still new to this

user1854183 Over a year ago

hi i have tried it, but i think i need to refrase my question. in some of the rows, i have more than one item with it's different fiels, so there can be two DRN, two CR, two DR two Amount and two Prodate in one row, how do i bring back the data of just the first item in the row? i hope this makes sense?

gbn Over a year ago

@user1854183: you're welcome. Please accept this answer then with the tock on the left. Thank you

Collectives™ on Stack Overflow

Extracting certain values from XML column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related