How do I embed this code to split a column into my query since I cannot create a function (yet)? 1

[TheBugSlayer]

I have a CASE and a SPECIMEN table with a one-to-many relationship. One case can have more than one specimen. The specimen has a pipe-separated ClinicalConditions field. I need to write a query that returns a row for each condition, along with some other columns from both tables.
I have writen obtained a function that splits a column given a separator but the database that I use is hosted by another company and it is going to take them about two months to implement my custom function.
In the meantime I NEED to write the query and modify an existing report.
My problem is how to build the functions body into my query.
Here is the gut of the function:

    DECLARE @textXML XML;
    SELECT    @textXML = CAST('<d>' + REPLACE('ROG-12/XYZ|ROG-4/XYZ', '|', '</d><d>') + '</d>' AS XML);

    SELECT  T.split.value('.', 'nvarchar(max)') AS data
    FROM    @textXML.nodes('/d') T(split)

My query looks like this

SELECT C.ObjectID,
       S.ClinicalConditions
FROM CaseTable C
JOIN SpecimenTable S ON S.[Case] = C.ObjectID
WHERE C.Number = 'CASE20120006'

Very simple.
So if it returns a row like this

ObjectID	ClinicalConditions
71823189	ROG-12/XYZ|ROG-4/XYZ

I want the new query to give me

ObjectID	ClinicalConditions
71823189	ROG-12/XYZ
71823189	ROG-4/XYZ

I am working on a solution for this and other situations. In the meantime if you have an idea how to best implement it let me know. I will post what I come up with.
Thanks in advance.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Try this:

SELECT F1.ObjectId,
       O.splitdata 
FROM   (
       SELECT C.ObjectId,
                 Cast('<X>'+replace(S.ClinicalConditions,'|','</X><X>')+'</X>' as XML) as xmlfilter 
       From   CaseTable As C
              JOIN SpecimenTable As S
                On S.[Case] = C.ObjectId
       )F1
       CROSS APPLY
         ( 
         SELECT fdata.D.value('.','varchar(50)') as splitdata 
         FROM   f1.xmlfilter.nodes('X') as fdata(D)
		 ) O

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks George! I was very close, but I was close to this solution. I just had to make one change. The last character in every ClinicalConditions columns that contains at least one condition is a pipe, which causes an additional row to be created with an empty value in the SplitData column. I had removed it to simplify the example. The final solution looks like this:

SELECT F1.ObjectId,
       O.SplitData 
FROM 
(
   SELECT C.ObjectId,
		  CAST('<X>' + 
		  REPLACE(S.RuleOuts,'|','</X><X>') + 
		  '</X>' AS XML) AS XMLFilter 
   From   CaseTable AS C
   JOIN SpecimenTable AS S	ON S.[Case] = C.ObjectId
) F1
CROSS APPLY
( 
 SELECT FData.D.value('.','VARCHAR(50)') AS SplitData 
 FROM   f1.XMLFilter.nodes('X') AS FData(D) [b]
 WHERE LEN(FData.D.value('.','VARCHAR(50)')) > 0 [/b]
) O

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)