SQL Server XML分解太慢了

I am trying to shred some XML in a field in a table but it's taking way too long. It took 24 minutes to decode one record into 3 columns and 4 rows.

我试图在表中的字段中分解一些XML，但这花费的时间太长了。将一条记录解码为3列4行需要24分钟。

SELECT 
   p.value('(./CLAIM_ROW/CLAIM_NUMBER)[1]', 'VARCHAR(10)') AS ClaimID,
   p.value('(./LINE_GROUP/LINE_ROW/CLAIM_LINE)[1]', 'VARCHAR(10)') AS ClaimLine,
   p.value('(./LINE_GROUP/LINE_ROW/PAY_PERCENT)[1]', 'VARCHAR(10)') AS PAY_PERCENT 
FROM (SELECT CAST(xmldata AS XML) xdata FROM claimextxml WHERE claimid = '16022E000000') AS t1 
CROSS APPLY xdata.nodes('/CLAIMCHECK') t(p) 

This can't be the correct way to do this. How should I be decoding this XML?

这不是正确的方法。如何解码这个XML?

XML:

<CLAIMCHECK xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
    <CLAIM_ROW>
        <ACCIDENT_CAUSE />
        <ACCOUNT>CAL</ACCOUNT>
        <AFFIL_TYPE>GROUP</AFFIL_TYPE>
        <BILL_CLASS_CODE/>
        <BYPASS_CC_IND />
        <CARRIER_ID>CAR0000000001  </CARRIER_ID>
        <CLAIM_NUMBER>16022E000000</CLAIM_NUMBER>
        <CLAIM_SOURCE_ID>3              </CLAIM_SOURCE_ID>
        <CLAIM_START_DATE>01/11/2016</CLAIM_START_DATE>
        <CLAIM_END_DATE>01/11/2016</CLAIM_END_DATE>
        <DATE_ONSET />
        <DOB>08/01/1999</DOB>
        <ELIGIBLE_ORG_ID>ELO0000000002  </ELIGIBLE_ORG_ID>
        <ENROLL_TYPE>I</ENROLL_TYPE>
        <ENROLLMENT_TYPE>S</ENROLLMENT_TYPE>
        <FACILITY_CODE/>
        <FREQUENCY_CODE>1</FREQUENCY_CODE>
        <CLAIM_TYPE>PROFESSIONAL</CLAIM_TYPE>
        <FORM_TYPE>1500</FORM_TYPE>
        <IS_AUTH>N</IS_AUTH>
        <IS_AUTO_ACCIDENT>N</IS_AUTO_ACCIDENT>
        <IS_EMPLOYMENT>N</IS_EMPLOYMENT>
        <IS_ITS_CLAIM>N</IS_ITS_CLAIM>
        <IS_OTHER_ACCIDENT>N</IS_OTHER_ACCIDENT>
        <ITS_TYPE />
        <COB_TYPE>Medicare</COB_TYPE>
        <COVERAGE_TYPE>HospitalOnly</COVERAGE_TYPE>
        <MEMBER_NO>MBR0000000000</MEMBER_NO>
        <PATIENT_SEX>M</PATIENT_SEX>
        <PAY_TO>AFL0000000000</PAY_TO>
        <PCP>N</PCP>
        <PHY_ZIP>93311</PHY_ZIP>
        <PLAN_ID>BPL0000000000</PLAN_ID>
        <PLAN_TYPE>Medical        </PLAN_TYPE>
        <PROCEDURE_COUNT>4</PROCEDURE_COUNT>
        <PROGRAM_ID>PGM0000000000</PROGRAM_ID>
        <PROGRAM_TYPE>Medicaid HMO</PROGRAM_TYPE>
        <PROVIDER>PRV000000</PROVIDER>
        <PROVIDER_TYPE>08</PROVIDER_TYPE>
        <CLAIM_VENDOR_FTIN />
        <ADDITIONAL_INFO_INDICATOR>N</ADDITIONAL_INFO_INDICATOR>
        <OPTIONAL_DATA />
        <REFER_TO>PRV000000</REFER_TO>
        <SEG_TYPE>INT</SEG_TYPE>
        <SORT_DATA />
        <SPECIALTY>GE             </SPECIALTY>
        <STATUS>OPEN</STATUS>
        <TOTAL_AMT>3500.00</TOTAL_AMT>
        <ABEND_CODE>NO</ABEND_CODE>
        <ABEND_MESSAGE/>
        <CLAIM_ERROR_CODE>00</CLAIM_ERROR_CODE>
        <CLAIM_ERROR_MESSAGE/>
        <CLAIMCHECK_IND>PEND-C</CLAIMCHECK_IND>
    </CLAIM_ROW>
    <DIAG_GROUP>
        <DIAG_ROW>
            <DIAG_NO>1</DIAG_NO>
            <DIAG_CODE>K63.5</DIAG_CODE>
            <DX_CODING_SYS>0</DX_CODING_SYS>
        </DIAG_ROW>
        <DIAG_ROW>
            <DIAG_NO>2</DIAG_NO>
            <DIAG_CODE>K57.30</DIAG_CODE>
            <DX_CODING_SYS>0</DX_CODING_SYS>
        </DIAG_ROW>
        <DIAG_ROW>
            <DIAG_NO>3</DIAG_NO>
            <DIAG_CODE>K64.9</DIAG_CODE>
            <DX_CODING_SYS>0</DX_CODING_SYS>
        </DIAG_ROW>
    </DIAG_GROUP>
    <LINE_GROUP>
        <LINE_ROW>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CHARGE>1200.00</CHARGE>
            <PX_TOTAL_AMOUNT>3500.0000</PX_TOTAL_AMOUNT>
            <PX_CLAIM_DRG_CODE />
            <CLAIM_LINE>1</CLAIM_LINE>
            <CODE_MODIFIER1>AG</CODE_MODIFIER1>
            <CODE_MODIFIER2/>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
            <PROCEDURE_CODE>45385</PROCEDURE_CODE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3 />
            <DIAG_PTR4 />
            <DIAG_PTR5 />
            <DIAG_PTR6 />
            <DIAG_PTR7 />
            <PX_DOS>01/11/2016</PX_DOS>
            <PX_POS>24</PX_POS>
            <PX_PROVIDER>PRV000000</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE             </PX_SPECIALTY>
            <PX_PROVIDER_STATUS>1</PX_PROVIDER_STATUS>
            <PX_CLAIM_VENDOR_FTIN />
            <PX_ADDITIONAL_INFO_INDICATOR>N</PX_ADDITIONAL_INFO_INDICATOR>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID />
            <REV_CODE />
            <SERVICE_UNITS>1</SERVICE_UNITS>
            <PX_TYPE_OF_SERVICE />
            <PX_CLAIM_TYPE>PROFESSIONAL</PX_CLAIM_TYPE>
            <PX_FORM_TYPE>1500</PX_FORM_TYPE>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_START_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_START_DATE>
            <PX_CLAIM_END_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_END_DATE>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <PX_BILL_CLASS_CODE/>
            <PX_FACILITY_CODE/>
            <PX_FREQUENCY_CODE>1</PX_FREQUENCY_CODE>
            <AUDIT_RESULT>000</AUDIT_RESULT>
            <AUDIT_DISPOSITION>OKAY</AUDIT_DISPOSITION>
            <AUDIT_MSG1/>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE/>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>ORG-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>FLT-C-C</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM/>
            <ORIGINAL_CODE/>
            <ORIGINAL_LINE/>
            <PAY_PERCENT/>
            <PAM_AUDIT_RESULT>FLT-C-C</PAM_AUDIT_RESULT>
            <PAM_CRITERIA_IND/>
            <PAM_CRITERIA_NO/>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED/>
            <PAM_POLICY_ID/>
            <RVU/>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM/>
            <SURVIVOR_CODE/>
            <SURVIVOR_LINE_NO/>
            <REPORT_CODE/>
        </LINE_ROW>
        <LINE_ROW>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CHARGE>1200.00</CHARGE>
            <PX_TOTAL_AMOUNT>3500.0000</PX_TOTAL_AMOUNT>
            <PX_CLAIM_DRG_CODE />
            <CLAIM_LINE>2</CLAIM_LINE>
            <CODE_MODIFIER1>59</CODE_MODIFIER1>
            <CODE_MODIFIER2>51</CODE_MODIFIER2>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
            <PROCEDURE_CODE>45384</PROCEDURE_CODE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3 />
            <DIAG_PTR4 />
            <DIAG_PTR5 />
            <DIAG_PTR6 />
            <DIAG_PTR7 />
            <PX_DOS>01/11/2016</PX_DOS>
            <PX_POS>24</PX_POS>
            <PX_PROVIDER>PRV004880</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE             </PX_SPECIALTY>
            <PX_PROVIDER_STATUS>1</PX_PROVIDER_STATUS>
            <PX_CLAIM_VENDOR_FTIN />
            <PX_ADDITIONAL_INFO_INDICATOR>N</PX_ADDITIONAL_INFO_INDICATOR>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID />
            <REV_CODE />
            <SERVICE_UNITS>1</SERVICE_UNITS>
            <PX_TYPE_OF_SERVICE />
            <PX_CLAIM_TYPE>PROFESSIONAL</PX_CLAIM_TYPE>
            <PX_FORM_TYPE>1500</PX_FORM_TYPE>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_START_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_START_DATE>
            <PX_CLAIM_END_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_END_DATE>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <PX_BILL_CLASS_CODE/>
            <PX_FACILITY_CODE/>
            <PX_FREQUENCY_CODE>1</PX_FREQUENCY_CODE>
            <AUDIT_RESULT>000</AUDIT_RESULT>
            <AUDIT_DISPOSITION>OKAY</AUDIT_DISPOSITION>
            <AUDIT_MSG1/>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE>000</DROPPED_DAY_RANGE>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>ORG-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>NON-C-N</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM/>
            <ORIGINAL_CODE/>
            <ORIGINAL_LINE>000</ORIGINAL_LINE>
            <PAY_PERCENT>050</PAY_PERCENT>
            <PAM_AUDIT_RESULT>NON-C-N</PAM_AUDIT_RESULT>
            <PAM_CRITERIA_IND>N</PAM_CRITERIA_IND>
            <PAM_CRITERIA_NO>0000</PAM_CRITERIA_NO>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED>0000</PAM_MAX_ALLOWED>
            <PAM_POLICY_ID/>
            <RVU>10.07</RVU>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM/>
            <SURVIVOR_CODE/>
            <SURVIVOR_LINE_NO/>
            <REPORT_CODE>NON-N-C</REPORT_CODE>
        </LINE_ROW>
        <LINE_ROW>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CHARGE>0</CHARGE>
            <PX_TOTAL_AMOUNT>3500.0000</PX_TOTAL_AMOUNT>
            <PX_CLAIM_DRG_CODE />
            <CLAIM_LINE>3</CLAIM_LINE>
            <CODE_MODIFIER1>59</CODE_MODIFIER1>
            <CODE_MODIFIER2>51</CODE_MODIFIER2>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
            <PROCEDURE_CODE>45381</PROCEDURE_CODE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3 />
            <DIAG_PTR4 />
            <DIAG_PTR5 />
            <DIAG_PTR6 />
            <DIAG_PTR7 />
            <PX_DOS>01/11/2016</PX_DOS>
            <PX_POS>24</PX_POS>
            <PX_PROVIDER>PRV000000</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE             </PX_SPECIALTY>
            <PX_PROVIDER_STATUS>1</PX_PROVIDER_STATUS>
            <PX_CLAIM_VENDOR_FTIN />
            <PX_ADDITIONAL_INFO_INDICATOR>N</PX_ADDITIONAL_INFO_INDICATOR>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID />
            <REV_CODE />
            <SERVICE_UNITS>1</SERVICE_UNITS>
            <PX_TYPE_OF_SERVICE />
            <PX_CLAIM_TYPE>PROFESSIONAL</PX_CLAIM_TYPE>
            <PX_FORM_TYPE>1500</PX_FORM_TYPE>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_START_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_START_DATE>
            <PX_CLAIM_END_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_END_DATE>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <PX_BILL_CLASS_CODE/>
            <PX_FACILITY_CODE/>
            <PX_FREQUENCY_CODE>1</PX_FREQUENCY_CODE>
            <AUDIT_RESULT>8292</AUDIT_RESULT>
            <AUDIT_DISPOSITION>PEND</AUDIT_DISPOSITION>
            <AUDIT_MSG1>Procedure has been replaced with modifier 51 removed.</AUDIT_MSG1>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE>000</DROPPED_DAY_RANGE>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>ORG-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>M51-R-C</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM/>
            <ORIGINAL_CODE/>
            <ORIGINAL_LINE>000</ORIGINAL_LINE>
            <PAY_PERCENT>100</PAY_PERCENT>
            <PAM_AUDIT_RESULT>NON-C-N</PAM_AUDIT_RESULT>
            <PAM_CRITERIA_IND/>
            <PAM_CRITERIA_NO>0000</PAM_CRITERIA_NO>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED>0000</PAM_MAX_ALLOWED>
            <PAM_POLICY_ID/>
            <RVU>11.09</RVU>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM>16022E000000</SURVIVOR_CLAIM>
            <SURVIVOR_CODE>45000</SURVIVOR_CODE>
            <SURVIVOR_LINE_NO>4</SURVIVOR_LINE_NO>
            <REPORT_CODE>M51-R-C</REPORT_CODE>
        </LINE_ROW>
        <LINE_ROW>
            <CHARGE>1100.00</CHARGE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PROCEDURE_CODE>45381</PROCEDURE_CODE>
            <PX_DOS>01/11/2016</PX_DOS>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_POS>24</PX_POS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3/>
            <DIAG_PTR4/>
            <DIAG_PTR5/>
            <DIAG_PTR6/>
            <DIAG_PTR7/>
            <PX_PROVIDER>PRV000000</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE</PX_SPECIALTY>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID/>
            <REV_CODE/>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <CLAIM_LINE>4</CLAIM_LINE>
            <CODE_MODIFIER1>59</CODE_MODIFIER1>
            <CODE_MODIFIER2/>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <RVU>11.09</RVU>
            <SERVICE_UNITS>001</SERVICE_UNITS>
            <AUDIT_RESULT>000</AUDIT_RESULT>
            <AUDIT_DISPOSITION>OKAY</AUDIT_DISPOSITION>
            <AUDIT_MSG1/>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE>000</DROPPED_DAY_RANGE>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>M51-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>NON-C-N</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM>16022E000000</ORIGINAL_CLAIM>
            <ORIGINAL_CODE>45381</ORIGINAL_CODE>
            <PAM_AUDIT_RESULT/>
            <PAM_CRITERIA_IND/>
            <PAM_CRITERIA_NO>0000</PAM_CRITERIA_NO>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED>0000</PAM_MAX_ALLOWED>
            <PAM_POLICY_ID/>
            <ORIGINAL_LINE>003</ORIGINAL_LINE>
            <PAY_PERCENT>100</PAY_PERCENT>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM/>
            <SURVIVOR_CODE/>
            <SURVIVOR_LINE_NO/>
            <REPORT_CODE>NON-N-C</REPORT_CODE>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
        </LINE_ROW>
    </LINE_GROUP>
</CLAIMCHECK>

I do not have the schema. I do not have permission to create objects on the database.

我没有模式。我没有权限在数据库上创建对象。

UPDATE:

With the help of users (esp. Shnugo), I have updated my query and have it mostly working. I am now just trying to get rid of some XML tags.

在用户(尤其是Shnugo)的帮助下，我更新了我的查询并使其大部分工作。我现在正在尝试删除一些XML标记。

SELECT 
   xdata.value('(/CLAIMCHECK/CLAIM_ROW/CLAIM_NUMBER)[1]', 'VARCHAR(100)') AS ClaimID, 
   CAST(xdata.query('/CLAIMCHECK/DIAG_GROUP/DIAG_ROW/DIAG_CODE') AS VARCHAR(100) ) AS DIAGs,
   p.value('(CLAIM_LINE)[1]', 'VARCHAR(100)') AS ClaimLine,
   p.value('(PAY_PERCENT)[1]', 'VARCHAR(100)') AS PAY_PERCENT
FROM (SELECT CAST(xmldata AS XML) xdata FROM claimextxml WHERE claimid = '16022E003981') AS t1 
CROSS APPLY xdata.nodes('/CLAIMCHECK/LINE_GROUP/LINE_ROW') t(p)  

Currently the DIAGs fields has the DIAG tags -

目前，DIAGs字段有DIAG标记-

<DIAG_CODE>K63.5</DIAG_CODE><DIAG_CODE>K57.30</DIAG_CODE><DIAG_CODE>K64.9</DIAG_CODE>

Is there a different XML way that would eliminate these without using REPLACE in the SQL?

有没有一种不同的XML方法可以在SQL中不使用REPLACE就能消除这些问题?

2 个解决方案

#1

EDIT: According to your updated query:

Just wrap your XQuery expression in data() should suffice:

只需将XQuery表达式封装在data()中即可:

(Attention: be aware to use the fitting claimid!)

(注意:注意使用合适的claimid!)

SELECT 
   xdata.value('(/CLAIMCHECK/CLAIM_ROW/CLAIM_NUMBER)[1]', 'VARCHAR(100)') AS ClaimID, 
   CAST(xdata.query('data(/CLAIMCHECK/DIAG_GROUP/DIAG_ROW/DIAG_CODE)') AS VARCHAR(100) ) AS DIAGs,
   p.value('(CLAIM_LINE)[1]', 'VARCHAR(100)') AS ClaimLine,
   p.value('(PAY_PERCENT)[1]', 'VARCHAR(100)') AS PAY_PERCENT
FROM (SELECT CAST(xmldata AS XML) xdata FROM claimextxml WHERE claimid = '16022E000000') AS t1 
CROSS APPLY xdata.nodes('/CLAIMCHECK/LINE_GROUP/LINE_ROW') t(p)  

The result

结果

16022E000000    K63.5 K57.30 K64.9  1   
16022E000000    K63.5 K57.30 K64.9  2   050
16022E000000    K63.5 K57.30 K64.9  3   100
16022E000000    K63.5 K57.30 K64.9  4   100

previous

I set up an example and tried to create it as close to yours as possible. With your query I get the answer in a glimpse... But your approach will deliver only one single row. Are you calling this in kind of a loop?

我建立了一个示例，并试图创建一个尽可能接近您的示例。有了你的问题，我马上就知道答案了……但是您的方法只提供一行。你是在循环中调用它吗?

Here's my test. At the end you'll find a query which comes back with all fields at once (I implemented just some examples...)

这是我的测试。最后，您会发现一个查询，它会同时返回所有字段(我只是实现了一些示例…)

You'd split this in two calls actually to avoid an unwanted cross join. Your diag group and your line group are kind of two separate 1:n relations... If you do not need the diag group, just take this away...

你会把它分成两个调用来避免不必要的交叉连接。diag组和line组是两个独立的1:n关系。如果你不需要diag组，就把它拿走…

CREATE TABLE claimextxml(claimid varchar(100),xmldata varchar(max));
INSERT INTO claimextxml VALUES('16022E000000','<CLAIMCHECK xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
    <CLAIM_ROW>
        <ACCIDENT_CAUSE />
        <ACCOUNT>CAL</ACCOUNT>
        <AFFIL_TYPE>GROUP</AFFIL_TYPE>
        <BILL_CLASS_CODE/>
        <BYPASS_CC_IND />
        <CARRIER_ID>CAR0000000001  </CARRIER_ID>
        <CLAIM_NUMBER>16022E000000</CLAIM_NUMBER>
        <CLAIM_SOURCE_ID>3              </CLAIM_SOURCE_ID>
        <CLAIM_START_DATE>01/11/2016</CLAIM_START_DATE>
        <CLAIM_END_DATE>01/11/2016</CLAIM_END_DATE>
        <DATE_ONSET />
        <DOB>08/01/1999</DOB>
        <ELIGIBLE_ORG_ID>ELO0000000002  </ELIGIBLE_ORG_ID>
        <ENROLL_TYPE>I</ENROLL_TYPE>
        <ENROLLMENT_TYPE>S</ENROLLMENT_TYPE>
        <FACILITY_CODE/>
        <FREQUENCY_CODE>1</FREQUENCY_CODE>
        <CLAIM_TYPE>PROFESSIONAL</CLAIM_TYPE>
        <FORM_TYPE>1500</FORM_TYPE>
        <IS_AUTH>N</IS_AUTH>
        <IS_AUTO_ACCIDENT>N</IS_AUTO_ACCIDENT>
        <IS_EMPLOYMENT>N</IS_EMPLOYMENT>
        <IS_ITS_CLAIM>N</IS_ITS_CLAIM>
        <IS_OTHER_ACCIDENT>N</IS_OTHER_ACCIDENT>
        <ITS_TYPE />
        <COB_TYPE>Medicare</COB_TYPE>
        <COVERAGE_TYPE>HospitalOnly</COVERAGE_TYPE>
        <MEMBER_NO>MBR0000000000</MEMBER_NO>
        <PATIENT_SEX>M</PATIENT_SEX>
        <PAY_TO>AFL0000000000</PAY_TO>
        <PCP>N</PCP>
        <PHY_ZIP>93311</PHY_ZIP>
        <PLAN_ID>BPL0000000000</PLAN_ID>
        <PLAN_TYPE>Medical        </PLAN_TYPE>
        <PROCEDURE_COUNT>4</PROCEDURE_COUNT>
        <PROGRAM_ID>PGM0000000000</PROGRAM_ID>
        <PROGRAM_TYPE>Medicaid HMO</PROGRAM_TYPE>
        <PROVIDER>PRV000000</PROVIDER>
        <PROVIDER_TYPE>08</PROVIDER_TYPE>
        <CLAIM_VENDOR_FTIN />
        <ADDITIONAL_INFO_INDICATOR>N</ADDITIONAL_INFO_INDICATOR>
        <OPTIONAL_DATA />
        <REFER_TO>PRV000000</REFER_TO>
        <SEG_TYPE>INT</SEG_TYPE>
        <SORT_DATA />
        <SPECIALTY>GE             </SPECIALTY>
        <STATUS>OPEN</STATUS>
        <TOTAL_AMT>3500.00</TOTAL_AMT>
        <ABEND_CODE>NO</ABEND_CODE>
        <ABEND_MESSAGE/>
        <CLAIM_ERROR_CODE>00</CLAIM_ERROR_CODE>
        <CLAIM_ERROR_MESSAGE/>
        <CLAIMCHECK_IND>PEND-C</CLAIMCHECK_IND>
    </CLAIM_ROW>
    <DIAG_GROUP>
        <DIAG_ROW>
            <DIAG_NO>1</DIAG_NO>
            <DIAG_CODE>K63.5</DIAG_CODE>
            <DX_CODING_SYS>0</DX_CODING_SYS>
        </DIAG_ROW>
        <DIAG_ROW>
            <DIAG_NO>2</DIAG_NO>
            <DIAG_CODE>K57.30</DIAG_CODE>
            <DX_CODING_SYS>0</DX_CODING_SYS>
        </DIAG_ROW>
        <DIAG_ROW>
            <DIAG_NO>3</DIAG_NO>
            <DIAG_CODE>K64.9</DIAG_CODE>
            <DX_CODING_SYS>0</DX_CODING_SYS>
        </DIAG_ROW>
    </DIAG_GROUP>
    <LINE_GROUP>
        <LINE_ROW>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CHARGE>1200.00</CHARGE>
            <PX_TOTAL_AMOUNT>3500.0000</PX_TOTAL_AMOUNT>
            <PX_CLAIM_DRG_CODE />
            <CLAIM_LINE>1</CLAIM_LINE>
            <CODE_MODIFIER1>AG</CODE_MODIFIER1>
            <CODE_MODIFIER2/>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
            <PROCEDURE_CODE>45385</PROCEDURE_CODE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3 />
            <DIAG_PTR4 />
            <DIAG_PTR5 />
            <DIAG_PTR6 />
            <DIAG_PTR7 />
            <PX_DOS>01/11/2016</PX_DOS>
            <PX_POS>24</PX_POS>
            <PX_PROVIDER>PRV000000</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE             </PX_SPECIALTY>
            <PX_PROVIDER_STATUS>1</PX_PROVIDER_STATUS>
            <PX_CLAIM_VENDOR_FTIN />
            <PX_ADDITIONAL_INFO_INDICATOR>N</PX_ADDITIONAL_INFO_INDICATOR>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID />
            <REV_CODE />
            <SERVICE_UNITS>1</SERVICE_UNITS>
            <PX_TYPE_OF_SERVICE />
            <PX_CLAIM_TYPE>PROFESSIONAL</PX_CLAIM_TYPE>
            <PX_FORM_TYPE>1500</PX_FORM_TYPE>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_START_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_START_DATE>
            <PX_CLAIM_END_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_END_DATE>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <PX_BILL_CLASS_CODE/>
            <PX_FACILITY_CODE/>
            <PX_FREQUENCY_CODE>1</PX_FREQUENCY_CODE>
            <AUDIT_RESULT>000</AUDIT_RESULT>
            <AUDIT_DISPOSITION>OKAY</AUDIT_DISPOSITION>
            <AUDIT_MSG1/>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE/>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>ORG-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>FLT-C-C</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM/>
            <ORIGINAL_CODE/>
            <ORIGINAL_LINE/>
            <PAY_PERCENT/>
            <PAM_AUDIT_RESULT>FLT-C-C</PAM_AUDIT_RESULT>
            <PAM_CRITERIA_IND/>
            <PAM_CRITERIA_NO/>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED/>
            <PAM_POLICY_ID/>
            <RVU/>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM/>
            <SURVIVOR_CODE/>
            <SURVIVOR_LINE_NO/>
            <REPORT_CODE/>
        </LINE_ROW>
        <LINE_ROW>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CHARGE>1200.00</CHARGE>
            <PX_TOTAL_AMOUNT>3500.0000</PX_TOTAL_AMOUNT>
            <PX_CLAIM_DRG_CODE />
            <CLAIM_LINE>2</CLAIM_LINE>
            <CODE_MODIFIER1>59</CODE_MODIFIER1>
            <CODE_MODIFIER2>51</CODE_MODIFIER2>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
            <PROCEDURE_CODE>45384</PROCEDURE_CODE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3 />
            <DIAG_PTR4 />
            <DIAG_PTR5 />
            <DIAG_PTR6 />
            <DIAG_PTR7 />
            <PX_DOS>01/11/2016</PX_DOS>
            <PX_POS>24</PX_POS>
            <PX_PROVIDER>PRV004880</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE             </PX_SPECIALTY>
            <PX_PROVIDER_STATUS>1</PX_PROVIDER_STATUS>
            <PX_CLAIM_VENDOR_FTIN />
            <PX_ADDITIONAL_INFO_INDICATOR>N</PX_ADDITIONAL_INFO_INDICATOR>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID />
            <REV_CODE />
            <SERVICE_UNITS>1</SERVICE_UNITS>
            <PX_TYPE_OF_SERVICE />
            <PX_CLAIM_TYPE>PROFESSIONAL</PX_CLAIM_TYPE>
            <PX_FORM_TYPE>1500</PX_FORM_TYPE>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_START_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_START_DATE>
            <PX_CLAIM_END_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_END_DATE>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <PX_BILL_CLASS_CODE/>
            <PX_FACILITY_CODE/>
            <PX_FREQUENCY_CODE>1</PX_FREQUENCY_CODE>
            <AUDIT_RESULT>000</AUDIT_RESULT>
            <AUDIT_DISPOSITION>OKAY</AUDIT_DISPOSITION>
            <AUDIT_MSG1/>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE>000</DROPPED_DAY_RANGE>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>ORG-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>NON-C-N</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM/>
            <ORIGINAL_CODE/>
            <ORIGINAL_LINE>000</ORIGINAL_LINE>
            <PAY_PERCENT>050</PAY_PERCENT>
            <PAM_AUDIT_RESULT>NON-C-N</PAM_AUDIT_RESULT>
            <PAM_CRITERIA_IND>N</PAM_CRITERIA_IND>
            <PAM_CRITERIA_NO>0000</PAM_CRITERIA_NO>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED>0000</PAM_MAX_ALLOWED>
            <PAM_POLICY_ID/>
            <RVU>10.07</RVU>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM/>
            <SURVIVOR_CODE/>
            <SURVIVOR_LINE_NO/>
            <REPORT_CODE>NON-N-C</REPORT_CODE>
        </LINE_ROW>
        <LINE_ROW>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CHARGE>0</CHARGE>
            <PX_TOTAL_AMOUNT>3500.0000</PX_TOTAL_AMOUNT>
            <PX_CLAIM_DRG_CODE />
            <CLAIM_LINE>3</CLAIM_LINE>
            <CODE_MODIFIER1>59</CODE_MODIFIER1>
            <CODE_MODIFIER2>51</CODE_MODIFIER2>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
            <PROCEDURE_CODE>45381</PROCEDURE_CODE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3 />
            <DIAG_PTR4 />
            <DIAG_PTR5 />
            <DIAG_PTR6 />
            <DIAG_PTR7 />
            <PX_DOS>01/11/2016</PX_DOS>
            <PX_POS>24</PX_POS>
            <PX_PROVIDER>PRV000000</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE             </PX_SPECIALTY>
            <PX_PROVIDER_STATUS>1</PX_PROVIDER_STATUS>
            <PX_CLAIM_VENDOR_FTIN />
            <PX_ADDITIONAL_INFO_INDICATOR>N</PX_ADDITIONAL_INFO_INDICATOR>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID />
            <REV_CODE />
            <SERVICE_UNITS>1</SERVICE_UNITS>
            <PX_TYPE_OF_SERVICE />
            <PX_CLAIM_TYPE>PROFESSIONAL</PX_CLAIM_TYPE>
            <PX_FORM_TYPE>1500</PX_FORM_TYPE>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_START_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_START_DATE>
            <PX_CLAIM_END_DATE>1/11/2016 12:00:00 AM</PX_CLAIM_END_DATE>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <PX_BILL_CLASS_CODE/>
            <PX_FACILITY_CODE/>
            <PX_FREQUENCY_CODE>1</PX_FREQUENCY_CODE>
            <AUDIT_RESULT>8292</AUDIT_RESULT>
            <AUDIT_DISPOSITION>PEND</AUDIT_DISPOSITION>
            <AUDIT_MSG1>Procedure has been replaced with modifier 51 removed.</AUDIT_MSG1>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE>000</DROPPED_DAY_RANGE>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>ORG-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>M51-R-C</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM/>
            <ORIGINAL_CODE/>
            <ORIGINAL_LINE>000</ORIGINAL_LINE>
            <PAY_PERCENT>100</PAY_PERCENT>
            <PAM_AUDIT_RESULT>NON-C-N</PAM_AUDIT_RESULT>
            <PAM_CRITERIA_IND/>
            <PAM_CRITERIA_NO>0000</PAM_CRITERIA_NO>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED>0000</PAM_MAX_ALLOWED>
            <PAM_POLICY_ID/>
            <RVU>11.09</RVU>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM>16022E000000</SURVIVOR_CLAIM>
            <SURVIVOR_CODE>45000</SURVIVOR_CODE>
            <SURVIVOR_LINE_NO>4</SURVIVOR_LINE_NO>
            <REPORT_CODE>M51-R-C</REPORT_CODE>
        </LINE_ROW>
        <LINE_ROW>
            <CHARGE>1100.00</CHARGE>
            <PX_CLAIM_NUM>16022E000000</PX_CLAIM_NUM>
            <PROCEDURE_CODE>45381</PROCEDURE_CODE>
            <PX_DOS>01/11/2016</PX_DOS>
            <THRU_DOS>01/11/2016</THRU_DOS>
            <PX_CLAIM_STATUS>OPEN</PX_CLAIM_STATUS>
            <PX_POS>24</PX_POS>
            <PX_CODING_SYS>0</PX_CODING_SYS>
            <PX_DX>K63.5</PX_DX>
            <DIAG_PTR1>2</DIAG_PTR1>
            <DIAG_PTR2>3</DIAG_PTR2>
            <DIAG_PTR3/>
            <DIAG_PTR4/>
            <DIAG_PTR5/>
            <DIAG_PTR6/>
            <DIAG_PTR7/>
            <PX_PROVIDER>PRV000000</PX_PROVIDER>
            <PX_PROVIDER_TYPE>08</PX_PROVIDER_TYPE>
            <PX_SPECIALTY>GE</PX_SPECIALTY>
            <PX_STATUS>OKAY</PX_STATUS>
            <REFERRAL_ID/>
            <REV_CODE/>
            <UNIT_EXP_LINE_IND>Y</UNIT_EXP_LINE_IND>
            <CLAIM_LINE>4</CLAIM_LINE>
            <CODE_MODIFIER1>59</CODE_MODIFIER1>
            <CODE_MODIFIER2/>
            <CODE_MODIFIER3/>
            <CODE_MODIFIER4/>
            <RVU>11.09</RVU>
            <SERVICE_UNITS>001</SERVICE_UNITS>
            <AUDIT_RESULT>000</AUDIT_RESULT>
            <AUDIT_DISPOSITION>OKAY</AUDIT_DISPOSITION>
            <AUDIT_MSG1/>
            <AUDIT_MSG2/>
            <DROPPED_DAY_RANGE>000</DROPPED_DAY_RANGE>
            <LINE_ERROR_CODE>00</LINE_ERROR_CODE>
            <LINE_ERROR_MESSAGE/>
            <LINE_ORIGINATION>M51-C</LINE_ORIGINATION>
            <ORG_AUDIT_RESULT>NON-C-N</ORG_AUDIT_RESULT>
            <ORIGINAL_CLAIM>16022E000000</ORIGINAL_CLAIM>
            <ORIGINAL_CODE>45381</ORIGINAL_CODE>
            <PAM_AUDIT_RESULT/>
            <PAM_CRITERIA_IND/>
            <PAM_CRITERIA_NO>0000</PAM_CRITERIA_NO>
            <PAM_CRITERIA_TYPE/>
            <PAM_MAX_ALLOWED>0000</PAM_MAX_ALLOWED>
            <PAM_POLICY_ID/>
            <ORIGINAL_LINE>003</ORIGINAL_LINE>
            <PAY_PERCENT>100</PAY_PERCENT>
            <SS_MSG_NO/>
            <SS_MSG_TXT/>
            <SS_STATUS/>
            <LINE_SS_ENTRY_NO>000</LINE_SS_ENTRY_NO>
            <SURVIVOR_CLAIM/>
            <SURVIVOR_CODE/>
            <SURVIVOR_LINE_NO/>
            <REPORT_CODE>NON-N-C</REPORT_CODE>
            <AMOUNT_PAID>0.00</AMOUNT_PAID>
            <CONTRACT_AMOUNT>0.00</CONTRACT_AMOUNT>
        </LINE_ROW>
    </LINE_GROUP>
</CLAIMCHECK>');

/* Your query

SELECT 
   p.value('(./CLAIM_ROW/CLAIM_NUMBER)[1]', 'VARCHAR(10)') AS ClaimID,
   p.value('(./LINE_GROUP/LINE_ROW/CLAIM_LINE)[1]', 'VARCHAR(10)') AS ClaimLine,
   p.value('(./LINE_GROUP/LINE_ROW/PAY_PERCENT)[1]', 'VARCHAR(10)') AS PAY_PERCENT 
FROM (SELECT CAST(xmldata AS XML) xdata FROM claimextxml WHERE claimid = '16022E000000') AS t1 
CROSS APPLY xdata.nodes('/CLAIMCHECK') t(p) 
*/

SELECT 
   xdata.value('(./CLAIM_ROW/CLAIM_NUMBER)[1]', 'VARCHAR(10)') AS ClaimID
   --add more fields from node "CLAIM_ROW" in the same way
  ,DiagRow.value('DIAG_NO[1]','int') AS DIAG_NO
  ,DiagRow.value('DIAG_CODE[1]','varchar(max)') AS DIAG_CODE
  ,DiagRow.value('DX_CODING_SYS[1]','varchar(max)') AS DX_CODING_SYS
  ,LineRow.value('AMOUNT_PAID[1]','decimal(10,4)') AS AMOUNT_PAID
  ,LineRow.value('CHARGE[1]','decimal(10,4)') AS CHARGE
  --add more fields from node /CLAIM_ROW/LINE_GROUP/LINE_ROW in the same way
FROM (SELECT CAST(xmldata AS XML) xdata FROM claimextxml WHERE claimid = '16022E000000') AS t1 
CROSS APPLY xdata.nodes('/CLAIMCHECK/DIAG_GROUP/DIAG_ROW') AS A(DiagRow)
CROSS APPLY xdata.nodes('/CLAIMCHECK/LINE_GROUP/LINE_ROW') AS B(LineRow) 


--DROP TABLE claimextxml;

#2

In answer to your updated question:

回答你更新后的问题:

SELECT 
   xdata.value('(/CLAIMCHECK/CLAIM_ROW/CLAIM_NUMBER)[1]', 'VARCHAR(100)') AS ClaimID, 
   STUFF((SELECT ', '+n.v.value('.','NVARCHAR(128)') FROM t.p.nodes('/CLAIMCHECK/DIAG_GROUP/DIAG_ROW/DIAG_CODE') AS n(v) FOR XML PATH('')),1,1,'') AS DIAGs,
   p.value('(CLAIM_LINE)[1]', 'VARCHAR(100)') AS ClaimLine,
   p.value('(PAY_PERCENT)[1]', 'VARCHAR(100)') AS PAY_PERCENT
FROM (SELECT CAST(xmldata AS XML) xdata FROM claimextxml WHERE claimid = '16022E000000') AS t1 
CROSS APPLY xdata.nodes('/CLAIMCHECK/LINE_GROUP/LINE_ROW') t(p)  

This would return the DIAGS as a comma separated list.

这将返回作为逗号分隔的列表的图表。