0
CREATE PROCEDURE [news01].[usp_generateXml]
    (@email NVARCHAR(356))
AS
BEGIN TRY
    DECLARE @terms TABLE(sourceName NVARCHAR(256),
                         url NVARCHAR(Max),
                         articles NVARCHAR(MAX))

INSERT INTO @terms(sourceName, url, articles)
    SELECT  
        (SELECT source_name FROM tbl_sources),
        (SELECT article_url FROM tbl_articles),
        (SELECT article_id FROM news01.tbl_articles 
         WHERE article_title LIKE CONCAT('%',alt.term, '%'))            
    FROM    
        news01.tbl_alertTerms alt
    JOIN
        news01.tbl_userProfiles ussr ON (alt.profile_id = ussr.profile_id)

    DECLARE @articles AS NVARCHAR(MAX)
    DECLARE @sourceName AS NVARCHAR(256)
    DECLARE @url AS NVARCHAR(MAX)

    DECLARE cursorSearch CURSOR FOR
        SELECT sourceName, url, articles 
        FROM @terms

    OPEN cursorName

    FETCH NEXT FROM cursorName INTO @sourceName, @url, @articles 

    WHILE @@FETCH_STATUS = 0
        SELECT  
            sourceName AS 'SourceName',
            url AS 'Url',
            articles AS 'Articles'
        FROM
            @terms
        FOR XML AUTO, ROOT('Articles');

    CLOSE cursorName

I want to output the XMl raw file(xml link) using the cursor. Each user has a term(example football), the loop has to find with articles has that key word and than output the xml file with all the articles, the articles url and the source name(example BBC).The user is selected by entering his email as a parameter.

Is the cursor being implemented correctly? What am I doing wrong?

Any help would be greatly appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
swenetodd
  • 1
  • 2
  • Your SELECT query for the @terms is wrong. You need to JOIN tbl_sources, tbl_articles_ and tbl_alertTerms in the proper way – Eralper May 23 '16 at 10:39
  • I cant they dont have a relationship between them, that's why i used a subquery – swenetodd May 23 '16 at 10:44
  • Additionally, you do not need to define a cursor loop for the items of @terms. A single SELECT will provide an XNL output as this sample with 2 rows: – Eralper May 23 '16 at 10:45
  • 1
    If they don't have a relation then you can use CROSS JOIN which multiplies the output rows. If one of the tables ( tbl_sources, tbl_articles) has more than one row the subquery will return error – Eralper May 23 '16 at 10:46
  • These tell you how to append XML documents: http://stackoverflow.com/questions/26473448/sql-server-append-xml-child-nodes-to-parent-node and http://stackoverflow.com/questions/11699914/concatenate-xml-without-type-casting-to-string . The rest of your query is probably not working as @Eralper indicated. – Alex May 23 '16 at 10:52
  • Your while is probably missing a `BEGIN` ... `END` where you do a `FETCH NEXT ...` right before the `END`. Assuming you want the query to be executed once for each row in the cursor. If not, you could remove the `WHILE` statement. – Mikael Eriksson May 23 '16 at 11:18

0 Answers0