cross apply and xml and distinct

Question

cross apply and xml and distinct

andrew 67979

Ten Centuries

Points: 1268
More actions
February 15, 2017 at 6:02 am

#340645

Hi All
This is a little bit involved (for me!) as it is on the edge of my understanding but I will do my best to explain
I have this query that we are generating from our software that I need to tweak
with cteCastToXML as (select AUDITLOG.RecNum,TableNo,TableName,PKValue,AUDITLOG.Donor_No,Name,Donor,UserId,Date,Time,ChangeType,ModuleId,Description, CASE WHEN details like 'RECORD DELETED' THEN CAST('<Record><Field/></Record>' as xml) ELSE CAST(details as xml) END as x from AUDITLOG left join DON0001 on AUDITLOG.Donor_No=DON0001.DONOR_NO) select 28407,ROW_NUMBER() OVER (Order By Date desc,Time desc),RecNum, 'Y0|'+IsNull(RTrim(Name),'')+'||||' from cteCastToXML cross apply x.nodes('/Record/Field') as Rec(Fld) where UserId='BRAGG' and Rec.Fld.value('@Name','varchar(32)') like 'External Ref' order by Date desc,Time desc;
This works fine and gives a result set
The XML contained in the details column can have several Field elements per record
Using the cross apply in the result set I get one row per <field> per auditlog row which is in many cases what I want
If I remove the cross apply then in the result set I get one row per auditlog row which sometimes is also what I want
However removing the cross apply obviously means I can no longer use Rec.Fld.value in the where clause
What I want to achive is keeping the cross apply so I can use it in the where and the select columns between the ||| (not shown) BUT still only return ONE row per auditlog row
So I thought I could do Select distinct 28407,ROW_NUMBER() .... but I cannot for the life of me get it to work beacuse I don't seem to be able to add things to the order by to keep it happy
I hope this all make some sense !
Just to add if the where clause results in several 'copies' of the same auditlog row due to the multiple <fields> contained in it I don't care which row I keep and how many are thrown away

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

andrew 67979 - Wednesday, February 15, 2017 6:02 AM
Hi All
This is a little bit involved (for me!) as it is on the edge of my understanding but I will do my best to explain
I have this query that we are generating from our software that I need to tweak
with cteCastToXML as (select AUDITLOG.RecNum,TableNo,TableName,PKValue,AUDITLOG.Donor_No,Name,Donor,UserId,Date,Time,ChangeType,ModuleId,Description, CASE WHEN details like 'RECORD DELETED' THEN CAST('<Record><Field/></Record>' as xml) ELSE CAST(details as xml) END as x from AUDITLOG left join DON0001 on AUDITLOG.Donor_No=DON0001.DONOR_NO) select 28407,ROW_NUMBER() OVER (Order By Date desc,Time desc),RecNum, 'Y0|'+IsNull(RTrim(Name),'')+'||||' from cteCastToXML cross apply x.nodes('/Record/Field') as Rec(Fld) where UserId='BRAGG' and Rec.Fld.value('@Name','varchar(32)') like 'External Ref' order by Date desc,Time desc;
This works fine and gives a result set
The XML contained in the details column can have several Field elements per record
Using the cross apply in the result set I get one row per <field> per auditlog row which is in many cases what I want
If I remove the cross apply then in the result set I get one row per auditlog row which sometimes is also what I want
However removing the cross apply obviously means I can no longer use Rec.Fld.value in the where clause
What I want to achive is keeping the cross apply so I can use it in the where and the select columns between the ||| (not shown) BUT still only return ONE row per auditlog row
So I thought I could do Select distinct 28407,ROW_NUMBER() .... but I cannot for the life of me get it to work beacuse I don't seem to be able to add things to the order by to keep it happy
I hope this all make some sense !
Just to add if the where clause results in several 'copies' of the same auditlog row due to the multiple <fields> contained in it I don't care which row I keep and how many are thrown away

Andrew, can you provide a handful of examples of the content of the "details" column please?
Here's the same query formatted for readability:

with cteCastToXML as (
       select
              AUDITLOG.RecNum,
              TableNo,
              TableName,
              PKValue,
              AUDITLOG.Donor_No,
              Name,
              Donor,
              UserId,
              Date,
              Time,
              ChangeType,
              ModuleId,
              Description,
              CASE -- The LIKE here won't work without wildcards - it's thesame as using =
                     WHEN details like 'RECORD DELETED' THEN CAST('<Record><Field/></Record>' as xml)
                     ELSE CAST(details as xml) END as x
       from AUDITLOG
       left join DON0001
              on AUDITLOG.Donor_No=DON0001.DONOR_NO
)

select
       28407,
       ROW_NUMBER() OVER (Order By Date desc,Time desc),
       RecNum,
       'Y0|'+IsNull(RTrim(Name),'')+'||||'
from cteCastToXML
cross apply x.nodes('/Record/Field') as Rec(Fld)
where UserId='BRAGG'
       and Rec.Fld.value('@Name','varchar(32)') like 'External Ref'
order by Date desc,Time desc;

[/code]

Edit: SQL Code flags still messing stuff up.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 2

Hi Chris

Thanks for the speedy reply

The details colmn contains data like this ....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thanks. Here you go:

;WITH cteCastToXML AS ( SELECT Donor_No, UserId, x = CAST(details AS xml) FROM ( VALUES (1, 'BRAGG', '<Record><Field Name="External Ref" From="Adams 10568" To="Adams 16108" Type="DF_ASCII"></Field></Record>'), (2, 'BRAGG', '<Record><Field Name="External Ref" From="Ainslie 10585" To="Ainslie 17297" Type="DF_ASCII"></Field></Record>'), (3, 'BRAGG', '<Record><Field Name="External Ref" From="Ainslie 10585" To="Ainslie 59586" Type="DF_ASCII"></Field></Record>'), (4, 'BRAGG', '<Record><Field Name="Payment Method" From="Direct Debit (Agency) (A)" To="Standing Order (S)" Type="DF_ASCII"></Field></Record>'), (5, 'BRAGG', '<Record><Field Name="Usual Payment" From="4" To="4.34" Type="DF_BCD"></Field></Record>'), (6, 'BRAGG', '<Record><Field Name="Ktcamount" From="0" To="5.66" Type="DF_BCD"></Field><Field Name="Ktcsource" From=" " To="LOTPRO" Type="DF_ASCII"></Field><Field Name="Ktcapplication" From=" " To="APPGEN" Type="DF_ASCII"></Field></Record>'), (7, 'BRAGG', '<Record><Field Name="Usual Payment" From="3" To="5" Type="DF_BCD"></Field></Record>'), (8, 'BRAGG', '<Record><Field Name="Payment Method" From="Postal Payment (M)" To="Payroll Giving (G)" Type="DF_ASCII"></Field><Field Name="Usual Payment" From="13" To="4.34" Type="DF_BCD"></Field><Field Name="Recruited By" From="NONE" To="!HQ" Type="DF_ASCII"></Field><Field Name="External Ref" From=" " To="026" Type="DF_ASCII"></Field><Field Name="Ledger" From="STANDARD" To="DEBENHAMS" Type="DF_ASCII"></Field><Field Name="Recruitmentdate" From=" " To="08/06/2014" Type="DF_DATE"></Field></Record>') ) d (Donor_No, UserId, details)

)

SELECT Donor_No, UserId, x FROM cteCastToXML CROSS APPLY ( SELECT n = 1 FROM x.nodes('/Record/Field') as Rec(Fld) WHERE Rec.Fld.value('@Name','varchar(32)') = 'External Ref' ) y WHERE UserId='BRAGG'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 4

Thanks very much indeed.

I think this does exactly what I need I just need to translate back in to my code with all its many variables etc

One thing that is puzzling me .... i'm guessing it is the n=1 that is cause the "distinct" part that is preventing row 8 being output multiple times ? .... but n does not seem to be defined anywhere ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

The n is just a placeholder, and in fact for multiple occurrences of the keyword you'd need TOP(1) n. To get around this, you could also use EXISTS like this:

SELECT

28407,

ROW_NUMBER() OVER (ORDER BY [Date] DESC, [Time] DESC),

RecNum,

'Y0|' + ISNULL(RTRIM([Name]),'') + '||||'

FROM cteCastToXML

WHERE UserId = 'BRAGG'

AND EXISTS (

SELECT 1

FROM x.nodes('/Record/Field') as Rec(Fld)

WHERE Rec.Fld.value('@Name','varchar(32)') = 'External Ref'

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 76944 More actions · Answer 6

andrew 67979 - Wednesday, February 15, 2017 7:01 AM
Thanks very much indeed.
I think this does exactly what I need I just need to translate back in to my code with all its many variables etc
One thing that is puzzling me .... i'm guessing it is the n=1 that is cause the "distinct" part that is preventing row 8 being output multiple times ? .... but n does not seem to be defined anywhere ?

The n=1 IS the definition. I tend to prefer 1 AS n when defining column names precisely because it's less confusing, but n=1 is a popular way to define column names.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA