Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unanswered: Using FOR XML issue in stored procedure

    I'm attempting to use the FOR XML command to return XML from a query into a variable in my stored procedure, the same as I've done 5 million times. For some reason, this one is giving me trouble (I may not have had enough coffee this morning). The only variable is that I'm using a UNION, which I don't do often. Here's my query:

    SELECT 'Condition' AS [@Name],MedicalHistoryCondition AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName1 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName2 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName3 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName4 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName5 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    FOR XML PATH('Attribute'), ROOT('ClientAttributes'),ELEMENTS XSINIL

    When I attempt to wrap it in () to set it to a variable it errors:

    Incorrect syntax near the keyword 'FOR'. Am I missing something? TIA.

  2. #2
    Join Date
    Feb 2006
    Posts
    9

    Fixed

    So I figured it out, you need to nest the query inside another for it to work, below is the working version:

    SET @Output = (SELECT A.* FROM
    (SELECT 'Condition' AS [@Name],MedicalHistoryCondition AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName1 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName2 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName3 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName4 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)
    UNION SELECT 'Drug' AS [@Name],MedicalHistoryPrescriptionName5 AS [@Value] FROM tblClientMedicalHistoryData WHERE MedicalHistoryClientID IN (SELECT ClientID FROM tblClientData WHERE ClientGroupID = 3704)) AS A
    FOR XML PATH('Attribute'), ROOT('ClientAttributes'),ELEMENTS XSINIL)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •