关键字“FROM”附近的语法不正确。 [英] Incorrect syntax near the keyword 'FROM'.

问题描述

我有一个应该创建XML输出文件的代码。我有一切正常工作，除了收到来自的错误：

I have a code that is supposed to create an XML output file. I have everything working except getting an error on the from:

有关为何会这样做的任何想法？

Any thoughts as to why this would be?

推荐答案

不运行它：语法上没有错误。

Without running it: It is syntactical error free.

以正确的格式开始，例如

Start with proper formatting, e.g.

SELECT (   SELECT   'ADD' AS [@action-code] ,
                    'CLINICAL' AS [@data] ,
                    'OUTPATIENT' AS [@type] ,
                    '1.0' AS [@version] ,
                    (   SELECT CONVERT(VARCHAR(10), CAST(GETDATE() AS DATE), 110) AS [create-date] ,
                               CONVERT(VARCHAR(5), GETDATE(), 108) AS [create-time] ,
                               'xxxxx' AS [create-by] ,
                               'SQL2008' AS version ,
                               'SQL' AS [create-by-tool]
                        FOR XML PATH('file-audit-data'), TYPE, ELEMENTS ) ,
                    (   SELECT [provider-id] ,
                               [npi] ,
                               (   SELECT [first-name] ,
                                          [last-name] ,
                                          birthdate ,
                                          sex ,
                                          race ,
                                          ethnic ,
                                          [postal-code] ,
                                          (   SELECT 'ED-THROUGHPUT' AS [@measure-set] ,
                                                     [encounter-date] ,
                                                     [arrival-time] ,
                                                     a.[patient-id] ,
                                                     (   SELECT DISCHGCODE AS [@answer-code] ,
                                                                'DISCHGCODE' AS [@question-cd] ,
                                                                '0' AS [@row-number]
                                                         FROM   POP2 detail
                                                         WHERE  detail.[patient-id] = POP2.[patient-id]
                                                         FOR XML PATH('detail'), TYPE ) ,
                                                     (   SELECT EDDEPARTDT AS [@answer-code] ,
                                                                'EDDEPARTDT' AS [@question-cd] ,
                                                                '0' AS [@row-number]
                                                         FROM   POP2 detail
                                                         WHERE  detail.[patient-id] = POP2.[patient-id]
                                                         FOR XML PATH('detail'), TYPE ) ,
                                                     (   SELECT EDDEPARTTM AS [@answer-code] ,
                                                                'EDDEPARTTM' AS [@question-cd] ,
                                                                '0' AS [@row-number]
                                                         FROM   POP2 detail
                                                         WHERE  detail.[patient-id] = POP2.[patient-id]
                                                         FOR XML PATH('detail'), TYPE ) ,
                                                     (   SELECT emcode AS [@answer-code] ,
                                                                'EMCODE' AS [@question-cd] ,
                                                                '0' AS [@row-number]
                                                         FROM   POP2 detail
                                                         WHERE  detail.[patient-id] = POP2.[patient-id]
                                                         FOR XML PATH('detail'), TYPE ) ,
                                                     (   SELECT PRINDX AS [@answer-code] ,
                                                                'PRINDX ' AS [@question-cd] ,
                                                                '0' AS [@row-number]
                                                         FROM   POP2 detail
                                                         WHERE  detail.[patient-id] = POP2.[patient-id]
                                                         FOR XML PATH('detail'), TYPE ) ,
                                                     (   SELECT pmtsrce AS [@answer-code] ,
                                                                'PMTSRCE' AS [@question-cd] ,
                                                                '0' AS [@row-number]
                                                         FROM   POP2 detail
                                                         WHERE  detail.[patient-id] = POP2.[patient-id]
                                                         FOR XML PATH('detail'), TYPE )
                                              FROM   POP2 x
                                              WHERE  x.[patient-id] = [mhtest].[dbo].[pop2].[patient-id]
                                              FOR XML PATH('encounter'), TYPE, ELEMENTS )
                                   FROM   [mhtest].[dbo].[vw_RPT_ADHOC_DATA_DUMP] z
                                   WHERE  z.[n_PATIENT_ID] = [mhtest].[dbo].[pop2].[patient-id]
                                   FOR XML PATH('patient'), TYPE, ELEMENTS )
                        FROM   [mhtest].[dbo].[pop2] z
                        WHERE  z.[patient-id] = [mhtest].[dbo].[pop2].[patient-id]
                        FOR XML PATH('provider'), TYPE, ELEMENTS )
           FROM     [mhtest].[dbo].[pop2] y
           WHERE    y.[patient-id] = [mhtest].[dbo].[pop2].[patient-id]
           ORDER BY NEWID()
           FOR XML PATH('submission'), TYPE, ELEMENTS ) AS OP18
INTO   TOP18z;

btw，我会通过规范化来简化核心。例如

btw, I would simplify the core by normalizing it. E.g.

DECLARE @POP2 TABLE (
    ID INT ,
    DISCHGCODE INT ,
    EDDEPARTDT INT
);
INSERT INTO @POP2
VALUES ( 1, 2, 3 );

-- INSTEAD of
SELECT 'ED-THROUGHPUT' AS [@measure-set] ,
       (   SELECT detail.DISCHGCODE AS [@answer-code] ,
                  'DISCHGCODE' AS [@question-cd] ,
                  '0' AS [@row-number]
           FROM   @POP2 detail
           FOR XML PATH('detail'), TYPE ) ,
       (   SELECT detail.EDDEPARTDT AS [@answer-code] ,
                  'EDDEPARTDT' AS [@question-cd] ,
                  '0' AS [@row-number]
           FROM   @POP2 detail
           FOR XML PATH('detail'), TYPE )
FROM   @POP2 x
FOR XML PATH('encounter'), TYPE, ELEMENTS;

/*
<encounter measure-set="ED-THROUGHPUT">
  <detail answer-code="2" question-cd="DISCHGCODE" row-number="0" />
  <detail answer-code="3" question-cd="EDDEPARTDT" row-number="0" />
</encounter>
*/

-- Normalized
WITH Normalized
AS ( SELECT *
     FROM   @POP2 P
         UNPIVOT (   [Value]
                     FOR [Attribute] IN ( DISCHGCODE, EDDEPARTDT )) U )
SELECT 'ED-THROUGHPUT' AS [encounter/@measure-set] ,
       (   SELECT N.Attribute AS [@question-cd] ,
                  N.Value AS [@answer-code]
           FROM   Normalized N
           FOR XML PATH('detail'), TYPE ) AS [encounter]
FOR XML PATH('patient'), TYPE;

/*
<patient>
  <encounter measure-set="ED-THROUGHPUT">
    <detail question-cd="DISCHGCODE" answer-code="2" />
    <detail question-cd="EDDEPARTDT" answer-code="3" />
  </encounter>
</patient>
*/

这篇关于关键字“FROM”附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！