-
January 7th, 2014, 08:11 PM
#1
How to add delimiter to the xml filed search result.
data stored in xml field
<taxonPath>
<source>
<string>edna-kla</string>
</source>
<taxon>
<entry>
<string>English</string>
</entry>
</taxon>
</taxonPath>
<taxonPath>
<source>
<string>edna-kla</string>
</source>
<taxon>
<entry>
<string>Mathematics</string>
</entry>
</taxon>
</taxonPath>
<taxonPath>
<source>
<string>edna-kla</string>
</source>
<taxon>
<entry>
<string>Science</string>
</entry>
</taxon>
</taxonPath>
--- query to retrieve subjects (entry)
DECLARE @UserId as uniqueidentifier
SET @UserId = '2FA02125-28B7-4022-AEFB-9FE900A249C7'
SELECT (SELECT DISTINCT
CONVERT(nvarchar(200), T.topic.query('declare namespace lom="http://ltsc.ieee.org/xsd/LOM"; lom:string/text()'))
FROM [dbo].[Question] q1
CROSS
APPLY metadata.nodes('declare namespace lom="http://ltsc.ieee.org/xsd/LOM";
/lom:lom/lom:classification/lom:taxonPath[lom:source="edna-kla"]/lom:taxon/lom:entry')
AS T (topic)
WHERE q1.id = q.id
FOR xml PATH (''))
AS [Subject]
FROM Question q
LEFT JOIN (SELECT
COUNT(test) AS timesUsed,
question
FROM TestQuestions
GROUP BY question) tq
ON (tq.question = q.id)
LEFT JOIN QuestionYearLevels qyl
ON (qyl.question = q.id)
WHERE q.createdBy = @UserId
AND instanceCopy = 0
ORDER BY q.changeDate DESC
-- Query result
EnglishMathematicsScience
English
EnglishMathematics
English
MathematicsScience
Mathematics
Question:
with above query, how can i add a space or common to separate the multiple subjects in the result?
Desirable result:
English,Mathematics,Science
English
English,Mathematics
English
Mathematics,Science
Mathematics
Thanks,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|