SQL Server XML Shredding is way too slow

Question

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.

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:

<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.

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 -

<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?

Possible duplicate of [The Best Way to shred XML data into SQL Server database columns](http://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns) — dfundako, Feb 25 '16 at 17:52
@dfundako - I don't think I can use the "Best Way" - it seems to work for a single large XML file while I am getting the XML from a field in a table for thousands of records at once. — Hannover Fist, Feb 25 '16 at 18:47
not exactly; he's defining a schema and using an XML index, which means you would need to store XML as XML, and not a varchar. You could try dumping the rows into a temporary table with a defined schema and index, and seeing if the shred goes faster. — Stuart Ainsworth, Feb 25 '16 at 18:59
@StuartAinsworth - I'll give that a try. I was hoping it was something in my syntax that was causing the issue. The XML is from some new software we have got that displays the XML data in a split second (for a single record). — Hannover Fist, Feb 25 '16 at 19:30
stupid question, but are you sure there's an index on claimid in claimextxml? — Bruce Dunwiddie, Feb 25 '16 at 20:08
@BruceDunwiddie - Actually, there is NOT an index. Unfortunately, it's part of a third-party software solution that I can't modify. — Hannover Fist, Feb 25 '16 at 21:49
The 'index' might be relevant if there are millions of rows and you search for them without an index... Try to do your query without the XML-shredding. How much is the shredding and how much is the "finding the right row"? — Shnugo, Feb 25 '16 at 21:56
@Shnugo - I don't think the index was the issue since I was only selecting one record in a subquery in the query that took 24 minutes to parse out 4 rows. — Hannover Fist, Feb 25 '16 at 22:44
I think I can bet my house on that fact that it does not take 24 minutes to parse 4 rows of xml text and that the entire problem is that you are performing a table scan to locate the 4 rows. If you want to verify, select the 4 rows into a temp table, see that it takes 24 minutes to populate the temp table, then run your xml query against the temp table with only four rows in it and see that it runs instantaneously. — Bruce Dunwiddie, Feb 25 '16 at 23:06
I would bet my house too, that your performance problem is not related to the XML shredding - unless you do things we don't know about (like loops)... Please check this out to get this clear! If so, you should change the title :-) — Shnugo, Feb 26 '16 at 07:38

Shnugo · Accepted Answer · 2016-02-26T20:52:46.007

EDIT: According to your updated query:

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

(Attention: be aware to use the fitting 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

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;

It looks like I posted a later modified version of my query - I was getting 4 lines but it was taking 24 minutes. Your query looks a lot better and it working faster but it's duplicating based on the Diag Row. — Hannover Fist, Feb 25 '16 at 21:35
@HannoverFist, that's why I wrote above to split this in two separate queries or to erase the diag parts if you don't need them. — Shnugo, Feb 25 '16 at 21:38
Ah yes - I see how that works now. The diags are part of the overall claim and not a single line. I wouldn't be able to consolidate them unless I did some ... hmm is there some better XML function to emulate STUFF FOR XML PATH? — Hannover Fist, Feb 25 '16 at 21:50
@HannoverFist, That's easy: Just let the first `CROSS APPLY` away and write all your Diag-values (example: `//DIAG_NO` -> see the double slash) and use `+` to write one after the other. There are just three of them... — Shnugo, Feb 25 '16 at 21:53
Unfortunately, there is no set number of diagnosis - there can be anywhere from 1 or more diags per claim. I was able to get the second one by hardcoding it - `xdata.value('(/CLAIMCHECK/DIAG_GROUP/DIAG_ROW/DIAG_CODE)[2]','VARCHAR(10)')` — Hannover Fist, Feb 25 '16 at 23:15
I didn't figure out how to use the `//` or `+` but I was able to concatenate the DIAGs using **.query** but they have the tags. `xdata.query('/CLAIMCHECK/DIAG_GROUP/DIAG_ROW/DIAG_CODE') AS DIAGs` Is there a way to fix this without REPLACE? (or a better way) — Hannover Fist, Feb 25 '16 at 23:37
@HannoverFist Do you know the usage of `data()` within `.query()`? Please use the edit option to state your **expected output** within your question. What you want should be fairly easy to achieve - but I don't know what you want :-) — Shnugo, Feb 26 '16 at 07:42
I have looked but didn't see anything about `data()` using .query. Unfortunately, googling gets everything that has data in it. :( I have updated my question with what I have so far. Now I am just looking to have the multiple Diagnosis in one field. If you have an example of the syntax, I can try it. — Hannover Fist, Feb 26 '16 at 19:10
Thanks - that's what I was wanting to do. I looked but couldn't find anything about the data(). If you have a link with a good overview I would appreciate it - we got this new software that uses some XML fields to store data so this is my first foray into it. — Hannover Fist, Feb 26 '16 at 21:12

score 2 · Answer 2 · answered Feb 26 '16 at 19:07

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.

answered Feb 26 '16 at 19:07

TT.

15,774
6
47
88

Hi TT., cool answer, `STUFF` and `FOR XML PATH` are great! Worth an upvote. But: Shortly I stumbled over XQuery function `data()`. Have a look at my edits. Might be interesting for you... – Shnugo Feb 26 '16 at 20:54
@Shnugo Hello again Schnugo =). Very interesting indeed! – TT. Feb 26 '16 at 20:58
I just started to find out what can be done *within* the XPath/XQuery expressions. There's incredibly much to find! Only back draw with `data()` is that one cannot define something else than a space. At least I did not find out how... – Shnugo Feb 26 '16 at 21:01
@Shnugo Yeah I just looked that function up and a space delimiter is what you get. The OP has choices =). – TT. Feb 26 '16 at 21:09

SQL Server XML Shredding is way too slow

XML:

UPDATE:

2 Answers2

EDIT: According to your updated query:

previous