Try this out for a size
😎
DECLARE @TXML XML = '<SEARCHREQUESTRESULT><DOCUMENTS><DOCUMENTDETAIL><DOCUMENTDATE/><CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT><BOOK/><PAGE/><REFERENCENUMBER>APPOINT TRUSTEE FOR POWER OF SALE</REFERENCENUMBER><NOTES/><RETURNEDTO/><PARTIES><PARTY><ID>940</ID><NAME>MARINER HOUSE COUNCIL OF CO-OWNERS INC</NAME><ADDITIONALNAME/><PARTYTYPEID>1</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>941</ID><NAME>DOYLE</NAME><ADDITIONALNAME>PATRICK F</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>942</ID><NAME>BETTISON</NAME><ADDITIONALNAME>DENNIS R</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>943</ID><NAME>APFFEL</NAME><ADDITIONALNAME>DARRELL A</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>944</ID><NAME>SHABOT</NAME><ADDITIONALNAME>S BENJAMIN</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>945</ID><NAME>BAKER</NAME><ADDITIONALNAME>ETHAN</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY></PARTIES><LEGALS/><LEGALDETAILS/><FEES/><ASSOCIATEDDOCUMENTS/><ID>752</ID><NAME>2014048673</NAME><DOCSTATE>A</DOCSTATE><RECORDEDDATETIME>8/27/2014 8:19:03 AM</RECORDEDDATETIME><TYPE><DOCUMENTTYPE><ID>583</ID><CODE>RESOLUTION</CODE><NAME>RESOLUTION</NAME><SESSIONID>0</SESSIONID></DOCUMENTTYPE></TYPE><PARTY1>MARINER HOUSE COUNCIL OF CO-OWNERS INC</PARTY1><ERLEVEL/><SOURCE/><PARTY2>DOYLE, PATRICK F</PARTY2><NUMBEROFPAGES>3</NUMBEROFPAGES><SESSIONID>0</SESSIONID></DOCUMENTDETAIL></DOCUMENTS></SEARCHREQUESTRESULT><SEARCHREQUESTRESULT><DOCUMENTS><DOCUMENTDETAIL><DOCUMENTDATE/><CONSIDERATIONAMOUNT>5750.00</CONSIDERATIONAMOUNT><BOOK/><PAGE/><REFERENCENUMBER/><NOTES/><RETURNEDTO/><PARTIES><PARTY><ID>946</ID><NAME>SHAFFER</NAME><ADDITIONALNAME>RICHARD PAUL</ADDITIONALNAME><PARTYTYPEID>1</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>947</ID><NAME>HOWARD</NAME><ADDITIONALNAME>BRUCE G</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY></PARTIES><LEGALS/><LEGALDETAILS/><FEES/><ASSOCIATEDDOCUMENTS/><ID>753</ID><NAME>2014048674</NAME><DOCSTATE>A</DOCSTATE><RECORDEDDATETIME>8/27/2014 8:32:55 AM</RECORDEDDATETIME><TYPE><DOCUMENTTYPE><ID>383</ID><CODE>A OF J</CODE><NAME>A OF J</NAME><SESSIONID>0</SESSIONID></DOCUMENTTYPE></TYPE><PARTY1>SHAFFER, RICHARD PAUL</PARTY1><ERLEVEL/><SOURCE/><PARTY2>HOWARD, BRUCE G</PARTY2><NUMBEROFPAGES>2</NUMBEROFPAGES><SESSIONID>0</SESSIONID></DOCUMENTDETAIL></DOCUMENTS></SEARCHREQUESTRESULT><SEARCHREQUESTRESULT><DOCUMENTS><DOCUMENTDETAIL><DOCUMENTDATE/><CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT><BOOK/><PAGE/><REFERENCENUMBER/><NOTES/><RETURNEDTO/><PARTIES><PARTY><ID>948</ID><NAME>DINH</NAME><ADDITIONALNAME>NU THI</ADDITIONALNAME><PARTYTYPEID>1</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY><PARTY><ID>949</ID><NAME>PHAM</NAME><ADDITIONALNAME>ANTHONY TAM</ADDITIONALNAME><PARTYTYPEID>2</PARTYTYPEID><TYPE/><ADDRESS1/><ADDRESS2/><CITY/><STATE/><ZIPCODE/><ATTRIBUTE/><SESSIONID>0</SESSIONID></PARTY></PARTIES><LEGALS><LEGALBASE><ID>17</ID><DESCRIPTION>JAMAICA BEACH L: 8 B: G PT: SEC 2</DESCRIPTION><NOTES/><PROPERTYNOTES/><LEGALTYPE>S</LEGALTYPE><SESSIONID>0</SESSIONID></LEGALBASE></LEGALS><LEGALDETAILS><SUBDIVIONLEGAL><LOT>8</LOT><OUTLOT/><BLOCK>G</BLOCK><ID>17</ID><DESCRIPTION>JAMAICA BEACH L: 8 B: G PT: SEC 2</DESCRIPTION><NOTES/><PROPERTYNOTES/><LEGALTYPE>S</LEGALTYPE><SESSIONID>0</SESSIONID></SUBDIVIONLEGAL></LEGALDETAILS><FEES/><ASSOCIATEDDOCUMENTS/><ID>754</ID><NAME>2014048675</NAME><DOCSTATE>A</DOCSTATE><RECORDEDDATETIME>8/27/2014 8:44:48 AM</RECORDEDDATETIME><TYPE><DOCUMENTTYPE><ID>3</ID><CODE>DEED</CODE><NAME>DEED</NAME><SESSIONID>0</SESSIONID></DOCUMENTTYPE></TYPE><PARTY1>DINH, NU THI</PARTY1><ERLEVEL/><SOURCE/><PARTY2>PHAM, ANTHONY TAM</PARTY2><NUMBEROFPAGES>2</NUMBEROFPAGES><SESSIONID>0</SESSIONID></DOCUMENTDETAIL></DOCUMENTS></SEARCHREQUESTRESULT>'
--select @TXML
SELECT
DOCUMENTDETAIL.DATA.value('CONSIDERATIONAMOUNT[1]' ,'DECIMAL(12,2)') AS DOC_CONSIDERATIONAMOUNT
,DOCUMENTDETAIL.DATA.value('REFERENCENUMBER[1]' ,'VARCHAR(50)') AS DOC_REFERENCENUMBER
,DOCUMENTDETAIL.DATA.value('ID[1]' ,'INT') AS DOC_ID
,DOCUMENTDETAIL.DATA.value('NAME[1]' ,'VARCHAR(50)') AS DOC_NAME
,DOCUMENTDETAIL.DATA.value('DOCSTATE[1]' ,'VARCHAR(50)') AS DOC_DOCSTATE
,DOCUMENTDETAIL.DATA.value('RECORDEDDATETIME[1]' ,'DATETIME') AS DOC_RECORDEDDATETIME
,DOCUMENTDETAIL.DATA.value('PARTY1[1]' ,'VARCHAR(50)') AS DOC_PARTY1
,DOCUMENTDETAIL.DATA.value('PARTY2[1]' ,'VARCHAR(50)') AS DOC_PARTY2
,DOCUMENTDETAIL.DATA.value('NUMBEROFPAGES[1]' ,'INT') AS DOC_NUMBEROFPAGES
,DOCUMENTDETAIL.DATA.value('SESSIONID[1]' ,'INT') AS DOC_SESSIONID
,PARTY.DATA.value('ID[1]' ,'INT') AS PARTY_ID
,PARTY.DATA.value('NAME[1]' ,'VARCHAR(50)') AS PARTY_NAME
,PARTY.DATA.value('ADDITIONALNAME[1]' ,'VARCHAR(50)') AS PARTY_ADDITIONALNAME
,PARTY.DATA.value('PARTYTYPEID[1]' ,'INT') AS PARTY_PARTYTYPEID
,PARTY.DATA.value('SESSIONID[1]' ,'INT') AS PARTY_SESSIONID
,DOCUMENTTYPE.DATA.value('ID[1]' ,'INT') AS DOCUMENTTYPE_ID
,DOCUMENTTYPE.DATA.value('CODE[1]' ,'VARCHAR(50)') AS DOCUMENTTYPE_CODE
,DOCUMENTTYPE.DATA.value('NAME[1]' ,'VARCHAR(50)') AS DOCUMENTTYPE_NAME
,DOCUMENTTYPE.DATA.value('SESSIONID[1]' ,'INT') AS DOCUMENTTYPE_ID
-- uncomment to inspect each node
--,SEARCHRESULTS.DATA.query('(.)') AS A_1
--,DOCUMENTDETAIL.DATA.query('(.)') AS A_2
--,PARTY.DATA.query('(.)') AS A_3
--,DOCUMENTTYPE.DATA.query('(.)') AS A_4
FROM @TXML.nodes('SEARCHREQUESTRESULT') AS SEARCHRESULTS(DATA)
OUTER APPLY SEARCHRESULTS.DATA.nodes('DOCUMENTS/DOCUMENTDETAIL') AS DOCUMENTDETAIL(DATA)
OUTER APPLY DOCUMENTDETAIL.DATA.nodes('PARTIES/PARTY') AS PARTY(DATA)
OUTER APPLY DOCUMENTDETAIL.DATA.nodes('TYPE/DOCUMENTTYPE') AS DOCUMENTTYPE(DATA)
Results
DOC_CONSIDERATIONAMOUNT DOC_REFERENCENUMBER DOC_ID DOC_NAME DOC_DOCSTATE DOC_RECORDEDDATETIME DOC_PARTY1 DOC_PARTY2 DOC_NUMBEROFPAGES DOC_SESSIONID PARTY_ID PARTY_NAME PARTY_ADDITIONALNAME PARTY_PARTYTYPEID PARTY_SESSIONID DOCUMENTTYPE_ID DOCUMENTTYPE_CODE DOCUMENTTYPE_NAME DOCUMENTTYPE_ID
------------------------ ---------------------------------- ------- ----------- ------------- ----------------------- ---------------------------------------- ------------------- ----------------- ------------- ----------- ---------------------------------------- --------------------- ----------------- --------------- --------------- ------------------ ------------------ ---------------
0.00 APPOINT TRUSTEE FOR POWER OF SALE 752 2014048673 A 2014-08-27 08:19:03.000 MARINER HOUSE COUNCIL OF CO-OWNERS INC DOYLE, PATRICK F 3 0 940 MARINER HOUSE COUNCIL OF CO-OWNERS INC 1 0 583 RESOLUTION RESOLUTION 0
0.00 APPOINT TRUSTEE FOR POWER OF SALE 752 2014048673 A 2014-08-27 08:19:03.000 MARINER HOUSE COUNCIL OF CO-OWNERS INC DOYLE, PATRICK F 3 0 941 DOYLE PATRICK F 2 0 583 RESOLUTION RESOLUTION 0
0.00 APPOINT TRUSTEE FOR POWER OF SALE 752 2014048673 A 2014-08-27 08:19:03.000 MARINER HOUSE COUNCIL OF CO-OWNERS INC DOYLE, PATRICK F 3 0 942 BETTISON DENNIS R 2 0 583 RESOLUTION RESOLUTION 0
0.00 APPOINT TRUSTEE FOR POWER OF SALE 752 2014048673 A 2014-08-27 08:19:03.000 MARINER HOUSE COUNCIL OF CO-OWNERS INC DOYLE, PATRICK F 3 0 943 APFFEL DARRELL A 2 0 583 RESOLUTION RESOLUTION 0
0.00 APPOINT TRUSTEE FOR POWER OF SALE 752 2014048673 A 2014-08-27 08:19:03.000 MARINER HOUSE COUNCIL OF CO-OWNERS INC DOYLE, PATRICK F 3 0 944 SHABOT S BENJAMIN 2 0 583 RESOLUTION RESOLUTION 0
0.00 APPOINT TRUSTEE FOR POWER OF SALE 752 2014048673 A 2014-08-27 08:19:03.000 MARINER HOUSE COUNCIL OF CO-OWNERS INC DOYLE, PATRICK F 3 0 945 BAKER ETHAN 2 0 583 RESOLUTION RESOLUTION 0
5750.00 753 2014048674 A 2014-08-27 08:32:55.000 SHAFFER, RICHARD PAUL HOWARD, BRUCE G 2 0 946 SHAFFER RICHARD PAUL 1 0 383 A OF J A OF J 0
5750.00 753 2014048674 A 2014-08-27 08:32:55.000 SHAFFER, RICHARD PAUL HOWARD, BRUCE G 2 0 947 HOWARD BRUCE G 2 0 383 A OF J A OF J 0
0.00 754 2014048675 A 2014-08-27 08:44:48.000 DINH, NU THI PHAM, ANTHONY TAM 2 0 948 DINH NU THI 1 0 3 DEED DEED 0
0.00 754 2014048675 A 2014-08-27 08:44:48.000 DINH, NU THI PHAM, ANTHONY TAM 2 0 949 PHAM ANTHONY TAM 2 0 3 DEED DEED 0