Find the worthless rows!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find the worthless rows!!

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-29 : 16:09:08

Let's assume that I am shredding some XML and I have put the data into a table with a identity on it. It will be possible for a section of the XML to be missing, empty, or filled. So the columns in the row may or may not have data.

Is there an easy/lazy/efficient way to determine that the row is of no value (ie. all columns null except id of course).

What I've been playing with:


Declare @t table (
	id int identity(1,1),
	a int,
	b varchar(10),
	c datetime,
	d bit
)

Insert Into @t Select null, null, null, null
Insert Into @t Select 111, '111', '1/1/2011', null
Insert Into @t Select 112, '112', null, null
Insert Into @t Select 113, null, '1/3/2011', null
Insert Into @t Select 114, null, null, null
Insert Into @t Select 115, '111', '1/1/2011', 1
Insert Into @t Select 116, '112', null, 1
Insert Into @t Select 117, null, '1/3/2011', 1
Insert Into @t Select 118, null, null, 1
Insert Into @t Select null, null, null, null

Select 
	*, 
	CHECKSUM(*),
	CHECKSUM(a,b,c),
	CHECKSUM(a,b,c,d),
	BINARY_CHECKSUM(*),
	BINARY_CHECKSUM(a,b,c),
	BINARY_CHECKSUM(a,b,c,d)
From @t

EDIT: fixed the code tag.

Corey

I Has Returned!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-29 : 16:29:28

How does the original xml document look like?
The .query function would return an empty space, I think.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-29 : 16:34:36

Depending on the datatypes for each column, try this

Select 
*, 
CASE
	WHEN COALESCE(a, b, c, d) IS NULL THEN 'Empty'
	ELSE 'Non-empty'
END AS Yak
From @t

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-29 : 16:43:12

See this

DECLARE	@Sample XML = '<root><yak><a>1</a></yak><yak></yak><yak><b>3</b></yak><yak><c></c></yak></root>'

SELECT	e.query('*')
FROM	@Sample.nodes('root/yak') AS n(e)

SELECT	e.query('*')
FROM	@Sample.nodes('root/yak') AS n(e)
WHERE	CAST(e.query('*') AS NVARCHAR(MAX)) > N''

As you can see, the c node is not missing. It's empty for values.

N 56°04'39.26"
E 12°55'05.63"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-29 : 22:06:10

I'll sort through these samples and see if they apply.

Unfortunately, I'm not working with such a flat structure. I'll see if I can put a better example together tomorrow.

Btw, you are correct that if the element is there an empty space is returned, and a null is returned when the element is missing.

I'm using XmlColumn.value('XPath', 'varchar(100)').

I thought about coalesce, and that might be what I go with in the end... but I just didn't want to have to maintain a full list of the columns (there are several more than 4). I was hoping there was a 'cheat'

Corey

I Has Returned!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-29 : 22:58:29

Well I came up with a 'generic' solution, but I'm not convinced it's the 'best' way.

This is a process that will only run once to 'shred' the file... but still. I don't want it to be a mess.

This is what I have:


--Same Table variable I used before

;with ToXml As (
	Select 
		o.id,
		RowXml = CONVERT(xml, RowXml)
	From @t o
	Outer Apply 
		(
		Select * 
		From @t i
		Where i.id = o.id
		For Xml Path('')
		) x(RowXml)
)

Select 
	A.*, 
	HasValues = case when CAST(B.RowXml.query('*[local-name() != "id"]') AS NVARCHAR(MAX)) <> N'' then 'Yes' else 'No' end
From @t A
Inner Join ToXml B
On A.id = B.id

The HasValues case would actually be a condition in my implementation... but it worked better there for demonstration.

Thoughts?

Corey

I Has Returned!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-30 : 07:39:32

You could also possibly use the exist() method of xml data type

HasValues = case when RowXml.exist('*[local-name() != "id"]') = 1 then 'Yes' else 'No' end

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-30 : 08:58:36

quote:
Originally posted by sunitabeck

You could also possibly use the exist() method of xml data type
HasValues = case when RowXml.exist('*[local-name() != "id"]') = 1 then 'Yes' else 'No' end

Yeah, I like this better. Seems cleaner. Probably more efficient too.

I'm still trying to get used to working with XML in SQL...

EDIT: Definitely more efficient


using .query()
(10 row(s) affected)
Table 'Worktable'. Scan count 8, logical reads 167, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#20340A56'. Scan count 12, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

using .exist()
(10 row(s) affected)
Table '#20340A56'. Scan count 12, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Corey

I Has Returned!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-30 : 14:19:19

Woohooo!! Finally I said something that is useful to you, even though it is about "worthless rows"

Would have been perfect if I could delete my reply now so you will sound like you are talking to yourself. Unfortunately, you quoted me :(

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-30 : 16:46:32

quote:
Originally posted by sunitabeck

Woohooo!! Finally I said something that is useful to you, even though it is about "worthless rows"

Would have been perfect if I could delete my reply now so you will sound like you are talking to yourself. Unfortunately, you quoted me :(

Thanks!

And.... Yes. Yes I did.

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-30 : 16:55:52

We should make both of you moderators so you can edit each other's replies.

/grabs popcorn

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-30 : 18:09:39

quote:
Originally posted by robvolk

We should make both of you moderators so you can edit each other's replies.

/grabs popcorn

I very reluctantly agree, it is a big responsibility. But, Rob, let us be kind to Corey he wouldn't want to take it on, so let us not make him one.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-05 : 06:26:41

quote:
Originally posted by sunitabeck

quote:
Originally posted by robvolk

We should make both of you moderators so you can edit each other's replies.

/grabs popcorn

I very reluctantly agree, it is a big responsibility. But, Rob, let us be kind to Corey he wouldn't want to take it on, so let us not make him one.

Oh, I See. I see how it is...

Just wait, I'll catch you with a great response... an *poof* there go my comments in the thread

Corey

I Has Returned!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 07:17:48

quote:
Originally posted by Seventhnight

Oh, I See. I see how it is...

Just wait, I'll catch you with a great response... an *poof* there go my comments in the thread

Corey

I Has Returned!!

No you won't! I learned from the very best!! I learned to "ALWAYS QUOTE"!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources