March 12, 2007 at 11:54 am
Hello!
Anybody know how to handle joining a key field from an xml document node to a relational field? I'm struggling over here with this small part in the query:
If Exists((Select cast(cast(node.ref.query('RowGUID/text()') as varchar(255)) as uniqueidentifier) INNER JOIN IFTTST_OLD.Transactions t
ON (????) = t.DeviceTransactionGuid
WHERE t.TransactionStatus = 1 ))
Here's the complete query I have so far:
Begin
Try
Set NoCount On
Begin Transaction
Print 'FileID RowGUID Transaction Index Found'
Print '==================================================================='
Declare content_cursor Cursor LOCAL For
Select Top 1000 FileID, [Content]
From IFT_DataSet WITH (NOLOCK)
Where IsCleaned = 0
and [Content] Is Not Null
Open content_cursor
Declare @FileID int, @Content xml
While 1 = 1
Begin
Fetch Next From content_cursor
Into @FileID, @Content
If @@Fetch_Status <> 0
Break
Declare @AllTrackDataEmpty bit
Set @AllTrackDataEmpty = 1
Declare guid_cursor cursor LOCAL For
Select cast(cast(node.ref.query('RowGUID/text()') as varchar(255)) as uniqueidentifier) As TransactionRowGUID
From @Content.nodes('/UPLOAD/TRANSACTIONS') node(ref)
Open guid_cursor
Declare @GUID uniqueidentifier, @guidCount as int
Set @guidCount = 1
While 1 = 1
Begin
Fetch Next From guid_cursor
Into @GUID
If @@Fetch_Status <> 0
Break
If Exists((SELECT @GUID FROM guid_cursor
INNER JOIN IFTTST_OLD.dbo.Transactions T
ON cc.rowguid = t.DeviceTransactionGuid
WHERE t.TransactionStatus = 1 ))
Begin
Print convert(varchar(5), @FileID) + ' ' + convert(varchar(100), @GUID) + ' ' + convert(varchar(10), @guidCount) + ' Found'
Update IFT_DataSet
Set [Content].modify('replace value of (/UPLOAD/TRANSACTIONS[position()=sql:variable("@guidCount")]/Track1/text())[1] with "" ')
Where FileID = @FileID
Update IFT_DataSet
Set [Content].modify('replace value of (/UPLOAD/TRANSACTIONS[position()=sql:variable("@guidCount")]/Track2/text())[1] with "" ')
Where FileID = @FileID
Update IFT_DataSet
Set [Content].modify('replace value of (/UPLOAD/TRANSACTIONS[position()=sql:variable("@guidCount")]/Track3/text())[1] with "" ')
Where FileID = @FileID
End
Else
Begin
Set @AllTrackDataEmpty = 0
Print convert(varchar(5), @FileID) + ' ' + convert(varchar(100), @GUID) + ' ' + convert(varchar(10), @guidCount)
End
Set @guidCount = @guidCount + 1
End
Close guid_cursor
Deallocate guid_cursor
If @AllTrackDataEmpty = 1
Begin
Update [IFT_DataSet]
Set IsCleaned = 1
Where FileID = @FileID
End
End
Close content_cursor
Deallocate content_cursor
Commit Transaction
Set NoCount Off
End
Try
Begin
Catch
Rollback Transaction
Set NoCount Off
End
Catch
March 13, 2007 at 12:48 pm
I had to look up domain data while extracting data out of an XML column. Did something like this:
from TableA
cross
apply TableA.FormData.nodes('//TrackingInfo') as details(xml_req)
left
join TrackingType_dm ttd on Description = xml_req.value('./Type[1]', 'varchar(50)')
Hope that helps getting you on the right track.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply