Rowsets from XML Data Columns

Question

Post reply

Rowsets from XML Data Columns

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
November 13, 2008 at 11:09 pm

#193792

Hello everyone. I hope everyone is doing good. Of course I am posting here due to an issue, so I will get to it.
I have a table with 6 columns:
Create Table Audit (ID INT identity(1,1), DateRecorded DATETIME, ModifiedBy TINYINT, DeletedData XML, InsertedData XML, RankTypeID INT)
-- forsome reason the xml will not display so I have attached an xls spread sheet for actual data
insert into Audit
values ('1/1/2007', 1, 'xml in attachment','xml in attachment', 1)
insert into Audit
values ('2/1/2007', 1, 'xml in attachment','xml in attachment', 2)
I need to be able to shred the data in the xml columns into rowsets.
id DateRecorded ModifiedyBy DeletedData InsertedData RankTypeID
1 1/1/2007 1 Attribute1 Attribute1 1
2 2/1/07 1 Attribute1 Attribute1 2
2 2/1/07 1 Attribute2 Attribute2 2
Now, what I need to do is
select DateRecorded, ModifiedBy, RankTypeID, DeletedData, InsertedData
From Table
where RankTypeID = 2
Where DeletedData and Inserted data is not in xml format, but has been pulled out into actual normal sql server data types.
Does anyone have any thoughts on the subject. I greatly appreciate it.
Fraggle

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

select DateRecorded, ModifiedBy, RankTypeID,

d.value('@LastModifiedBy','varchar(50)') as Deleted_LastModifiedBy,

d.value('@DateLastModified','datetime') as Deleted_DateLastModified,

d.value('@Description','varchar(100)') as Deleted_Description,

d.value('@PayoutMethodTypeID','int') as Deleted_PayoutMethodTypeID,

d.value('@Active','int') as Deleted_Active,

i.value('@LastModifiedBy','varchar(50)') as Inserted_LastModifiedBy,

i.value('@DateLastModified','datetime') as Inserted_DateLastModified,

i.value('@Description','varchar(100)') as Inserted_Description,

i.value('@PayoutMethodTypeID','int') as Inserted_PayoutMethodTypeID,

i.value('@Active','int') as Inserted_Active

From audit

cross apply DeletedData.nodes('/Deleted') as a(d)

cross apply InsertedData.nodes('/Inserted') as b(i)

where RankTypeID = 2

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 2

Thanks Mark. That did the trick. Thanks for the assist.

Fraggle

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 3

Mark,

I realize I am getting picky now, but how would you go about joining a table to this? So instead of return RankTypeID, I would rather return the actual RankDescription.

Thanks,

Fraggle

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Just join to another table as normal

select t.DateRecorded, t.ModifiedBy, r.RankDescription,

d.value('@LastModifiedBy','varchar(50)') as Deleted_LastModifiedBy,

d.value('@DateLastModified','datetime') as Deleted_DateLastModified,

d.value('@Description','varchar(100)') as Deleted_Description,

d.value('@PayoutMethodTypeID','int') as Deleted_PayoutMethodTypeID,

d.value('@Active','int') as Deleted_Active,

i.value('@LastModifiedBy','varchar(50)') as Inserted_LastModifiedBy,

i.value('@DateLastModified','datetime') as Inserted_DateLastModified,

i.value('@Description','varchar(100)') as Inserted_Description,

i.value('@PayoutMethodTypeID','int') as Inserted_PayoutMethodTypeID,

i.value('@Active','int') as Inserted_Active

From audit t

cross apply t.DeletedData.nodes('/Deleted') as a(d)

cross apply t.InsertedData.nodes('/Inserted') as b(i)

inner join othertable r on r.RankTypeID=t.RankTypeID

where t.RankTypeID = 2

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 5

Mark,

Sorry for the confusion, cause I know you had to be asking yourself, "what is this guy doing running a database if he can't join tables". I actually meant how do you join on one of the xml columns that is being shredded. Specifically, payoutmethodstypeid, as I would rather display the description that then number.

Thanks,

Fraggle

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

See if this helps

select t.DateRecorded, t.ModifiedBy, t.RankTypeID,

d.value('@LastModifiedBy','varchar(50)') as Deleted_LastModifiedBy,

d.value('@DateLastModified','datetime') as Deleted_DateLastModified,

d.value('@Description','varchar(100)') as Deleted_Description,

d.value('@PayoutMethodTypeID','int') as Deleted_PayoutMethodTypeID,

d.value('@Active','int') as Deleted_Active,

i.value('@LastModifiedBy','varchar(50)') as Inserted_LastModifiedBy,

i.value('@DateLastModified','datetime') as Inserted_DateLastModified,

i.value('@Description','varchar(100)') as Inserted_Description,

r.Inserted_PayoutMethodTypeDescription,

i.value('@Active','int') as Inserted_Active

From audit t

cross apply t.DeletedData.nodes('/Deleted') as a(d)

cross apply t.InsertedData.nodes('/Inserted') as b(i)

inner join othertable r on r.PayoutMethodTypeID=i.value('@PayoutMethodTypeID','int')

where t.RankTypeID = 2

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537