XQuery help please.

Question

Post reply

XQuery help please.

CptCrusty1

Hall of Fame

Points: 3386
More actions
September 19, 2014 at 8:31 pm

#293339

I'm have a Node in an XML string that I need to extract it's child nodes in order... What I should have is:
PartyTypeID Name AdditionalName
1 MICKEY MOUSE
2 DUCK DONALD
2 DUCK DIASEY
I'm getting closer, but I'm missing something critical.... Any help is greatly appreciated....
SELECT
Kachunk.query('
for $n in /SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY/NAME
where /SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY/PARTYTYPEID = 1
return string($n)
') Party_1
FROM
[dbo].[XML_Convert]
<?xml version="1.0"?>
-<SEARCHREQUESTRESULT>
-<DOCUMENTS>
-<DOCUMENTDETAIL>
<DOCUMENTDATE/>
<CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT>
-<BOOK>
<![CDATA[]]>
</BOOK>
-<PAGE>
<![CDATA[]]>
</PAGE>
-<REFERENCENUMBER>
<![CDATA[]]>
</REFERENCENUMBER>
-<NOTES>
<![CDATA[]]>
</NOTES>
<RETURNEDTO/>
-<PARTIES>
-<PARTY>
<ID>1090</ID>
-<NAME>
<![CDATA[MICKEY MOUSE]]>
</NAME>
-<ADDITIONALNAME>
<![CDATA[]]>
</ADDITIONALNAME>
<PARTYTYPEID>1</PARTYTYPEID>
-<TYPE>
<![CDATA[]]>
</TYPE>
-<ADDRESS1>
<![CDATA[]]>
</ADDRESS1>
-<ADDRESS2>
<![CDATA[]]>
</ADDRESS2>
-<CITY>
<![CDATA[]]>
</CITY>
-<STATE>
<![CDATA[]]>
</STATE>
-<ZIPCODE>
<![CDATA[]]>
</ZIPCODE>
-<ATTRIBUTE>
<![CDATA[]]>
</ATTRIBUTE>
<SESSIONID>0</SESSIONID>
</PARTY>
-<PARTY>
<ID>1091</ID>
-<NAME>
<![CDATA[DUCK]]>
</NAME>
-<ADDITIONALNAME>
<![CDATA[DONALD]]>
</ADDITIONALNAME>
<PARTYTYPEID>2</PARTYTYPEID>
-<TYPE>
<![CDATA[]]>
</TYPE>
-<ADDRESS1>
<![CDATA[]]>
</ADDRESS1>
-<ADDRESS2>
<![CDATA[]]>
</ADDRESS2>
-<CITY>
<![CDATA[]]>
</CITY>
-<STATE>
<![CDATA[]]>
</STATE>
-<ZIPCODE>
<![CDATA[]]>
</ZIPCODE>
-<ATTRIBUTE>
<![CDATA[]]>
</ATTRIBUTE>
<SESSIONID>0</SESSIONID>
</PARTY>
-<PARTY>
<ID>1092</ID>
-<NAME>
<![CDATA[DUCK]]>
</NAME>
-<ADDITIONALNAME>
<![CDATA[DAISEY]]>
</ADDITIONALNAME>
<PARTYTYPEID>2</PARTYTYPEID>
-<TYPE>
<![CDATA[]]>
</TYPE>
-<ADDRESS1>
<![CDATA[]]>
</ADDRESS1>
-<ADDRESS2>
<![CDATA[]]>
</ADDRESS2>
-<CITY>
<![CDATA[]]>
</CITY>
-<STATE>
<![CDATA[]]>
</STATE>
-<ZIPCODE>
<![CDATA[]]>
</ZIPCODE>
-<ATTRIBUTE>
<![CDATA[]]>
</ATTRIBUTE>
<SESSIONID>0</SESSIONID>
</PARTY>

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick suggestion, use the nodes method, check out this example

😎

DECLARE @TXML XML = '<?xml version="1.0"?>

<SEARCHREQUESTRESULT>

<DOCUMENTS>

<DOCUMENTDETAIL>

<DOCUMENTDATE />

<CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT>

<BOOK><![CDATA[]]></BOOK>

<PAGE><![CDATA[]]></PAGE>

<REFERENCENUMBER><![CDATA[]]></REFERENCENUMBER>

<NOTES><![CDATA[]]></NOTES>

<RETURNEDTO/>

<PARTIES>

<PARTY>

<ID>1090</ID>

<NAME><![CDATA[MICKEY MOUSE]]></NAME>

<ADDITIONALNAME><![CDATA[]]></ADDITIONALNAME>

<PARTYTYPEID>1</PARTYTYPEID>

<TYPE><![CDATA[]]></TYPE>

<ADDRESS1><![CDATA[]]></ADDRESS1>

<ADDRESS2><![CDATA[]]></ADDRESS2>

<CITY><![CDATA[]]></CITY>

<STATE><![CDATA[]]></STATE>

<ZIPCODE><![CDATA[]]></ZIPCODE>

<ATTRIBUTE><![CDATA[]]></ATTRIBUTE>

<SESSIONID>0</SESSIONID>

</PARTY>

<PARTY>

<ID>1091</ID>

<NAME><![CDATA[DUCK]]></NAME>

<ADDITIONALNAME><![CDATA[DONALD]]></ADDITIONALNAME>

<PARTYTYPEID>2</PARTYTYPEID>

<TYPE><![CDATA[]]></TYPE>

<ADDRESS1><![CDATA[]]></ADDRESS1>

<ADDRESS2><![CDATA[]]></ADDRESS2>

<CITY><![CDATA[]]></CITY>

<STATE><![CDATA[]]></STATE>

<ZIPCODE><![CDATA[]]></ZIPCODE>

<ATTRIBUTE><![CDATA[]]></ATTRIBUTE>

<SESSIONID>0</SESSIONID>

</PARTY>

<PARTY>

<ID>1092</ID>

<NAME><![CDATA[DUCK]]></NAME>

<ADDITIONALNAME><![CDATA[DAISEY]]></ADDITIONALNAME>

<PARTYTYPEID>2</PARTYTYPEID>

<TYPE><![CDATA[]]></TYPE>

<ADDRESS1><![CDATA[]]></ADDRESS1>

<ADDRESS2><![CDATA[]]></ADDRESS2>

<CITY><![CDATA[]]></CITY>

<STATE><![CDATA[]]></STATE>

<ZIPCODE><![CDATA[]]></ZIPCODE>

<ATTRIBUTE><![CDATA[]]></ATTRIBUTE>

<SESSIONID>0</SESSIONID>

</PARTY>

</PARTIES>

</DOCUMENTDETAIL>

</DOCUMENTS>

</SEARCHREQUESTRESULT>';

SELECT

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS PARTY_RID

,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.query('.')

FROM @TXML.nodes('/SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY') AS PARTY(DATA);

Results

PARTY_RID PARTY_ID PARTY_NAME PARTY_ADDITIONALNAME

---------- --------- ------------- ---------------------

1 1090 MICKEY MOUSE

2 1091 DUCK DONALD

3 1092 DUCK DAISEY

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 2

CptCrusty1

Hall of Fame

Points: 3386

September 20, 2014 at 6:38 am

#1746872

If this works I am going to name my kids after you

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

CptCrusty1 (9/20/2014)
If this works I am going to name my kids after you

Good stuff, make certain you get the spelling right:-D

😎

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 4

Ok dude... could you look at this and see if there is an easier way? Thy Column I have listed as DocumentID is pivotal to the entire recordset. What's worse, is this is only 1 recordset, I have 2500 I'm trying to... understand. Normally, the client provides me with csv or delimited text files which is EASY... but the XSD they sent with this stuff doesn't work so I"m hosed.... Anyway.... Heres the script that works with the XML below:

With Doc as (

SELECT 1 as JoinCol, DOCUMENTDETAIL.DATA.value('NAME[1]','VARCHAR(50)') DocumentID

FROM @TXML.nodes('/SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL') AS DOCUMENTDETAIL(DATA)

),

GrGe as (

SELECT 1 AS JoinCol,

--ROW_NUMBER() OVER

-- (

-- ORDER BY (SELECT NULL)

-- ) AS RID,

PARTY.DATA.value('PARTYTYPEID[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.query('.')

--,DOCUMENTDETAIL.DATA.value('NAME[1]','VARCHAR(50)')

FROM @TXML.nodes('/SEARCHREQUESTRESULT/DOCUMENTS/DOCUMENTDETAIL/PARTIES/PARTY') AS PARTY(DATA)

)

Select D.DocumentID, G.Party_ID, G.Party_Name, G.PARTY_ADDITIONALNAME

from Doc D INNER JOIN GrGe G

ON D.JoinCol = G.JoinCol

<?xml version="1.0"?>

-<SEARCHREQUESTRESULT>

-<DOCUMENTS>

-<DOCUMENTDETAIL>

<DOCUMENTDATE/>

<CONSIDERATIONAMOUNT>0</CONSIDERATIONAMOUNT>

-<BOOK>

<![CDATA[]]>

</BOOK>

-<PAGE>

<![CDATA[]]>

</PAGE>

-<REFERENCENUMBER>

<![CDATA[]]>

</REFERENCENUMBER>

-<NOTES>

<![CDATA[]]>

</NOTES>

<RETURNEDTO/>

-<PARTIES>

-<PARTY>

<ID>1090</ID>

-<NAME>

<![CDATA[MORTGAGE ELECTRONIC REGISTRATION SYSTEM]]>

</NAME>

-<ADDITIONALNAME>

<![CDATA[]]>

</ADDITIONALNAME>

<PARTYTYPEID>1</PARTYTYPEID>

-<TYPE>

<![CDATA[]]>

</TYPE>

-<ADDRESS1>

<![CDATA[]]>

</ADDRESS1>

-<ADDRESS2>

<![CDATA[]]>

</ADDRESS2>

-<CITY>

<![CDATA[]]>

</CITY>

-<STATE>

<![CDATA[]]>

</STATE>

-<ZIPCODE>

<![CDATA[]]>

</ZIPCODE>

-<ATTRIBUTE>

<![CDATA[]]>

</ATTRIBUTE>

<SESSIONID>0</SESSIONID>

</PARTY>

-<PARTY>

<ID>1091</ID>

-<NAME>

<![CDATA[GATES]]>

</NAME>

-<ADDITIONALNAME>

<![CDATA[DANIEL]]>

</ADDITIONALNAME>

<PARTYTYPEID>2</PARTYTYPEID>

-<TYPE>

<![CDATA[]]>

</TYPE>

-<ADDRESS1>

<![CDATA[]]>

</ADDRESS1>

-<ADDRESS2>

<![CDATA[]]>

</ADDRESS2>

-<CITY>

<![CDATA[]]>

</CITY>

-<STATE>

<![CDATA[]]>

</STATE>

-<ZIPCODE>

<![CDATA[]]>

</ZIPCODE>

-<ATTRIBUTE>

<![CDATA[]]>

</ATTRIBUTE>

<SESSIONID>0</SESSIONID>

</PARTY>

-<PARTY>

<ID>1092</ID>

-<NAME>

<![CDATA[GATES]]>

</NAME>

-<ADDITIONALNAME>

<![CDATA[KIMBERLY]]>

</ADDITIONALNAME>

<PARTYTYPEID>2</PARTYTYPEID>

-<TYPE>

<![CDATA[]]>

</TYPE>

-<ADDRESS1>

<![CDATA[]]>

</ADDRESS1>

-<ADDRESS2>

<![CDATA[]]>

</ADDRESS2>

-<CITY>

<![CDATA[]]>

</CITY>

-<STATE>

<![CDATA[]]>

</STATE>

-<ZIPCODE>

<![CDATA[]]>

</ZIPCODE>

-<ATTRIBUTE>

<![CDATA[]]>

</ATTRIBUTE>

<SESSIONID>0</SESSIONID>

</PARTY>

</PARTIES>

-<LEGALS>

-<LEGALBASE>

<ID>65</ID>

-<DESCRIPTION>

<![CDATA[344 BRANDY RIDGE LANE, LEAGUE CITY, TX 77573]]>

</DESCRIPTION>

-<NOTES>

<![CDATA[]]>

</NOTES>

-<PROPERTYNOTES>

<![CDATA[]]>

</PROPERTYNOTES>

-<LEGALTYPE>

<![CDATA[A]]>

</LEGALTYPE>

<SESSIONID>0</SESSIONID>

</LEGALBASE>

</LEGALS>

-<LEGALDETAILS>

-<ADDRESSLEGAL>

-<HOUSENUMBER>

<![CDATA[344]]>

</HOUSENUMBER>

-<STREET>

<![CDATA[BRANDY RIDGE LANE]]>

</STREET>

-<STREETDIRECTION>

<![CDATA[]]>

</STREETDIRECTION>

-<CITY>

<![CDATA[LEAGUE CITY]]>

</CITY>

-<STATE>

<![CDATA[TX]]>

</STATE>

-<ZIPCODE>

<![CDATA[77573]]>

</ZIPCODE>

<ID>65</ID>

-<DESCRIPTION>

<![CDATA[344 BRANDY RIDGE LANE, LEAGUE CITY, TX 77573]]>

</DESCRIPTION>

-<NOTES>

<![CDATA[]]>

</NOTES>

-<PROPERTYNOTES>

<![CDATA[]]>

</PROPERTYNOTES>

-<LEGALTYPE>

<![CDATA[A]]>

</LEGALTYPE>

<SESSIONID>0</SESSIONID>

</ADDRESSLEGAL>

</LEGALDETAILS>

<FEES/>

-<ASSOCIATEDDOCUMENTS>

-<ASSOCIATEDDOCUMENT>

<ID>0</ID>

-<NAME>

<![CDATA[2007044874]]>

</NAME>

-<DOCSTATE>

<![CDATA[]]>

</DOCSTATE>

<RECORDEDDATETIME/>

-<TYPE>

-<DOCUMENTTYPE>

<ID>0</ID>

-<CODE>

<![CDATA[]]>

</CODE>

-<NAME>

<![CDATA[RELEASE]]>

</NAME>

<SESSIONID>0</SESSIONID>

</DOCUMENTTYPE>

</TYPE>

-<PARTY1>

<![CDATA[]]>

</PARTY1>

-<ERLEVEL>

<![CDATA[]]>

</ERLEVEL>

-<SOURCE>

<![CDATA[]]>

</SOURCE>

-<PARTY2>

<![CDATA[]]>

</PARTY2>

<NUMBEROFPAGES>0</NUMBEROFPAGES>

<SESSIONID>0</SESSIONID>

</ASSOCIATEDDOCUMENT>

</ASSOCIATEDDOCUMENTS>

<ID>834</ID>

-<NAME>

<![CDATA[2014048700]]>

</NAME>

-<DOCSTATE>

<![CDATA[A]]>

</DOCSTATE>

<RECORDEDDATETIME>8/27/2014 12:07:11 PM</RECORDEDDATETIME>

-<TYPE>

-<DOCUMENTTYPE>

<ID>577</ID>

-<CODE>

<![CDATA[RELEASE]]>

</CODE>

-<NAME>

<![CDATA[RELEASE]]>

</NAME>

<SESSIONID>0</SESSIONID>

</DOCUMENTTYPE>

</TYPE>

-<PARTY1>

<![CDATA[MORTGAGE ELECTRONIC REGISTRATION SYSTEM]]>

</PARTY1>

-<ERLEVEL>

<![CDATA[]]>

</ERLEVEL>

-<SOURCE>

<![CDATA[]]>

</SOURCE>

-<PARTY2>

<![CDATA[GATES, DANIEL]]>

</PARTY2>

<NUMBEROFPAGES>3</NUMBEROFPAGES>

<SESSIONID>0</SESSIONID>

</DOCUMENTDETAIL>

</DOCUMENTS>

</SEARCHREQUESTRESULT>'

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 5

To give you an idea of what I'm facing... Here's a short piece of the full XML issue....

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

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

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 7

OUTER APPLY??? What's this OUTER APPLY stuff?? No one told me about that???

This XQuery stuff is SEXY!!

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

CptCrusty1 (9/21/2014)
OUTER APPLY??? What's this OUTER APPLY stuff?? No one told me about that???
This XQuery stuff is SEXY!!

Outer apply acts as a left outer join, otherwise empty nodes would not show up.

😎