Newbie: SQL Query containing logic

Question

Newbie: SQL Query containing logic

Viewing 7 posts - 16 through 21 (of 21 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

Also note that both table names have spelling mistakes in your sample code. You need to correct them before running the code.

.

Garadin One Orange Chip Points: 29613 More actions · Answer 2

I see that the editor removed XML tags from my post and that is why you dont get the correct results.

Heh, good, I kept trying it and didn't get anything either, but that explains it. I really need to do some more research on the new coding techniques in 2k5 and 2k8. So many cool things to explore, not the least of which is XML.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

sonashish SSC Enthusiast Points: 107 More actions · Answer 3

Jacob

Thanks for pointing in spelling mistake, it was typo in post. I tried your syntax, but it is not returning any result. However If I run following query, it is working correctly.

*---------------------

SELECT

MyLine1, MyLine2 ,

MyLine3, CAST('{i}' + REPLACE(error_cds, ' ', '{/i}{i}') + '{/i}' AS XML) AS ERROR_CDS

FROM dbo.MyLine

*---------------------

ANy other clue?

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

The problem seems to be a copy paste error, and since XML tags are not displayed in the post, it is bit hard to debug. The same example is posted in this article: http://www.sqlserverandxml.com/2008/08/xquery-lab-19-how-to-parse-delimited.html

can you give it a try and see if it works?

.

sonashish SSC Enthusiast Points: 107 More actions · Answer 5

Hello Jacob

I found the problem. Actuall we are using { in CAST syntax. It wont work with { , if you use < in place of {. It is working, I dont know why., may be I am using SQL2K5

As I am new bie in sql, can you explain what is the 1st query function and what is cross apply and how it will be helping us specially following

CROSS APPLY ERROR_CDS.nodes('//i') x(i)

INNER JOIN AIRERROR t2 ON t2.code = x.i.value('.', 'INT')

Again thanks a lot

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

HI,

This forum does not allow to post code with XML tags. Hence I replaced XML tags with "{}" and added a note to you, to replace them with XML tags before running the code at your side. It looks like you did not notice it.

This query uses the XML data type methods introduced by SQL Server 2005. The first part of the query converts the delimited string to an XML data type and then uses XQuery methods to convert it to a result set. The results of the XQuery operation is joined with the parent table using CROSS APPLY.

Please refer books online for a detailed discussion on XML data type.

.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 7

sonashish (10/7/2008)
Hello
Thanks for the response. Actually this database is very old 5-6 year old, client doesnt want to change the data structure, so I have no option, I have to continue as it is.
I am managing 3 code maximum error codes. how can I add logic for checking logic for 3rd error code?

So if the data comes in this way (multiple values in 1 field), is it acceptable to parse it out on insert and put it in a more normalized structure?

With a primary key? and maybe a datetimestamp?

You went from 2 codes to 3 pretty quickly, there might be other changes the client has in mind.

The xml looks like an option, although I'd still like to see more structure. Without even a date, seems odd to query for all of time everytime. Maybe I'm just missing something.

Greg E