May 19, 2020 at 5:39 am
I was wondering whether anyone would have any suggestions on how to rewrite the following query to speed it up. I have looked at the execution plan and I am struggling to know where to begin. I assume I should be making more use of temp tables than one huge query, but I'm not sure where I should start breaking it up.
DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT,
'<root><row><id>[object]</id><text>Project</text><icon>true</icon><li_attr><id>[object]</id></li_attr><a_attr><href>#</href><id>[object]_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data /><parent>#</parent></row><row><id>46A35569-A2A5-4AAD-8B76-F1F6E7100670</id><text>BUILDING APPROVALS</text><icon>true</icon><li_attr><id>46A35569-A2A5-4AAD-8B76-F1F6E7100670</id></li_attr><a_attr><href>#</href><id>46A35569-A2A5-4AAD-8B76-F1F6E7100670_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>BUILDING APPROVALS</physicalPath><documentCount>0</documentCount><documentTypeCount>0</documentTypeCount></data><parent>[object]</parent></row><row><id>636ED389-4432-48B6-9983-3C3AD7EC23B8</id><text>Amended Approvals</text><icon>true</icon><li_attr><id>636ED389-4432-48B6-9983-3C3AD7EC23B8</id></li_attr><a_attr><href>#</href><id>636ED389-4432-48B6-9983-3C3AD7EC23B8_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Amended Approvals</physicalPath><documentCount>0</documentCount><documentTypeCount>1</documentTypeCount></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_1</id><text>Approved Documents</text><icon>true</icon><li_attr><id>j1_1</id></li_attr><a_attr><href>#</href><id>j1_1_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Approved Documents</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_2</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_2</id></li_attr><a_attr><href>#</href><id>j1_2_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_3</id><text>Covenant</text><icon>true</icon><li_attr><id>j1_3</id></li_attr><a_attr><href>#</href><id>j1_3_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Covenant</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_4</id><text>Knock Down Rebuild Requirements</text><icon>true</icon><li_attr><id>j1_4</id></li_attr><a_attr><href>#</href><id>j1_4_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Knock Down Rebuild Requirements</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_5</id><text>Pre-Lodgement</text><icon>true</icon><li_attr><id>j1_5</id></li_attr><a_attr><href>#</href><id>j1_5_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Pre-Lodgement</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_6</id><text>Request for Information</text><icon>true</icon><li_attr><id>j1_6</id></li_attr><a_attr><href>#</href><id>j1_6_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request for Information</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_7</id><text>Site Specific Reports</text><icon>true</icon><li_attr><id>j1_7</id></li_attr><a_attr><href>#</href><id>j1_7_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Site Specific Reports</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_9</id><text>CERTIFICATES</text><icon>true</icon><li_attr><id>j1_9</id></li_attr><a_attr><href>#</href><id>j1_9_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CERTIFICATES</physicalPath></data><parent>[object]</parent></row><row><id>j1_10</id><text>CONSTRUCTION</text><icon>true</icon><li_attr><id>j1_10</id></li_attr><a_attr><href>#</href><id>j1_10_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CONSTRUCTION</physicalPath></data><parent>[object]</parent></row><row><id>j1_18</id><text>Customer Obligations</text><icon>true</icon><li_attr><id>j1_18</id></li_attr><a_attr><href>#</href><id>j1_18_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Customer Obligations</physicalPath></data><parent>j1_10</parent></row><row><id>j1_17</id><text>Mandatory Stage Inspections [NSW Only]</text><icon>true</icon><li_attr><id>j1_17</id></li_attr><a_attr><href>#</href><id>j1_17_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Mandatory Stage Inspections [NSW Only]</physicalPath></data><parent>j1_10</parent></row><row><id>j1_16</id><text>Quality assurance Audits</text><icon>true</icon><li_attr><id>j1_16</id></li_attr><a_attr><href>#</href><id>j1_16_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Quality assurance Audits</physicalPath></data><parent>j1_10</parent></row><row><id>j1_15</id><text>Site Safety Audits</text><icon>true</icon><li_attr><id>j1_15</id></li_attr><a_attr><href>#</href><id>j1_15_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Site Safety Audits</physicalPath></data><parent>j1_10</parent></row><row><id>j1_11</id><text>CONTRACT DOCUMENTS</text><icon>true</icon><li_attr><id>j1_11</id></li_attr><a_attr><href>#</href><id>j1_11_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CONTRACT DOCUMENTS</physicalPath></data><parent>[object]</parent></row><row><id>j1_19</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_19</id></li_attr><a_attr><href>#</href><id>j1_19_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>true</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_11</parent></row><row><id>j1_20</id><text>Signed Documents</text><icon>true</icon><li_attr><id>j1_20</id></li_attr><a_attr><href>#</href><id>j1_20_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_21</id><text>Supporting Contract Documents</text><icon>true</icon><li_attr><id>j1_21</id></li_attr><a_attr><href>#</href><id>j1_21_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Supporting Contract Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_22</id><text>To Be Emailed</text><icon>true</icon><li_attr><id>j1_22</id></li_attr><a_attr><href>#</href><id>j1_22_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>To Be Emailed</physicalPath></data><parent>j1_11</parent></row><row><id>j1_23</id><text>Unsigned Documents</text><icon>true</icon><li_attr><id>j1_23</id></li_attr><a_attr><href>#</href><id>j1_23_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_24</id><text>Word Documents</text><icon>true</icon><li_attr><id>j1_24</id></li_attr><a_attr><href>#</href><id>j1_24_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Word Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_12</id><text>CORRESPONDENCE</text><icon>true</icon><li_attr><id>j1_12</id></li_attr><a_attr><href>#</href><id>j1_12_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CORRESPONDENCE</physicalPath></data><parent>[object]</parent></row><row><id>j1_30</id><text>Job Not Proceeding</text><icon>true</icon><li_attr><id>j1_30</id></li_attr><a_attr><href>#</href><id>j1_30_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Job Not Proceeding</physicalPath></data><parent>j1_12</parent></row><row><id>j1_29</id><text>On-Site</text><icon>true</icon><li_attr><id>j1_29</id></li_attr><a_attr><href>#</href><id>j1_29_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>On-Site</physicalPath></data><parent>j1_12</parent></row><row><id>j1_26</id><text>Pre-Site</text><icon>true</icon><li_attr><id>j1_26</id></li_attr><a_attr><href>#</href><id>j1_26_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Pre-Site</physicalPath></data><parent>j1_12</parent></row><row><id>j1_27</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_27</id></li_attr><a_attr><href>#</href><id>j1_27_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_26</parent></row><row><id>j1_13</id><text>DRAFTING</text><icon>true</icon><li_attr><id>j1_13</id></li_attr><a_attr><href>#</href><id>j1_13_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>DRAFTING</physicalPath></data><parent>[object]</parent></row><row><id>j1_33</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_33</id></li_attr><a_attr><href>#</href><id>j1_33_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_13</parent></row><row><id>j1_31</id><text>Contour</text><icon>true</icon><li_attr><id>j1_31</id></li_attr><a_attr><href>#</href><id>j1_31_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Contour</physicalPath></data><parent>j1_13</parent></row><row><id>j1_32</id><text>Engineering Design</text><icon>true</icon><li_attr><id>j1_32</id></li_attr><a_attr><href>#</href><id>j1_32_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Engineering Design</physicalPath></data><parent>j1_13</parent></row><row><id>j1_34</id><text>Plans</text><icon>true</icon><li_attr><id>j1_34</id></li_attr><a_attr><href>#</href><id>j1_34_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Plans</physicalPath></data><parent>j1_13</parent></row><row><id>j1_36</id><text>Tillings</text><icon>true</icon><li_attr><id>j1_36</id></li_attr><a_attr><href>#</href><id>j1_36_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tillings</physicalPath></data><parent>j1_13</parent></row><row><id>j1_35</id><text>Truss Slab PDFs</text><icon>true</icon><li_attr><id>j1_35</id></li_attr><a_attr><href>#</href><id>j1_35_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Truss Slab PDFs</physicalPath></data><parent>j1_13</parent></row><row><id>j1_14</id><text>FINANCE</text><icon>true</icon><li_attr><id>j1_14</id></li_attr><a_attr><href>#</href><id>j1_14_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>FINANCE</physicalPath></data><parent>[object]</parent></row><row><id>j1_37</id><text>Debt Collection</text><icon>true</icon><li_attr><id>j1_37</id></li_attr><a_attr><href>#</href><id>j1_37_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Debt Collection</physicalPath></data><parent>j1_14</parent></row><row><id>j1_38</id><text>OWNER ITEMS</text><icon>true</icon><li_attr><id>j1_38</id></li_attr><a_attr><href>#</href><id>j1_38_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>OWNER ITEMS</physicalPath></data><parent>[object]</parent></row><row><id>j1_39</id><text>Approvals</text><icon>true</icon><li_attr><id>j1_39</id></li_attr><a_attr><href>#</href><id>j1_39_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Approvals</physicalPath></data><parent>j1_38</parent></row><row><id>j1_40</id><text>Denied Requests</text><icon>true</icon><li_attr><id>j1_40</id></li_attr><a_attr><href>#</href><id>j1_40_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Denied Requests</physicalPath></data><parent>j1_38</parent></row><row><id>j1_41</id><text>Specification Documents</text><icon>true</icon><li_attr><id>j1_41</id></li_attr><a_attr><href>#</href><id>j1_41_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Specification Documents</physicalPath></data><parent>j1_38</parent></row><row><id>j1_42</id><text>PRODUCTION ESTIMATING</text><icon>true</icon><li_attr><id>j1_42</id></li_attr><a_attr><href>#</href><id>j1_42_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>PRODUCTION ESTIMATING</physicalPath></data><parent>[object]</parent></row><row><id>j1_46</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_46</id></li_attr><a_attr><href>#</href><id>j1_46_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_42</parent></row><row><id>j1_45</id><text>Correspondence</text><icon>true</icon><li_attr><id>j1_45</id></li_attr><a_attr><href>#</href><id>j1_45_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Correspondence</physicalPath></data><parent>j1_42</parent></row><row><id>j1_43</id><text>Orders</text><icon>true</icon><li_attr><id>j1_43</id></li_attr><a_attr><href>#</href><id>j1_43_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Orders</physicalPath></data><parent>j1_42</parent></row><row><id>j1_47</id><text>Amended Orders</text><icon>true</icon><li_attr><id>j1_47</id></li_attr><a_attr><href>#</href><id>j1_47_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Amended Orders</physicalPath></data><parent>j1_43</parent></row><row><id>j1_44</id><text>Workup Documents</text><icon>true</icon><li_attr><id>j1_44</id></li_attr><a_attr><href>#</href><id>j1_44_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Workup Documents</physicalPath></data><parent>j1_42</parent></row><row><id>j1_48</id><text>QUOTES</text><icon>true</icon><li_attr><id>j1_48</id></li_attr><a_attr><href>#</href><id>j1_48_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>QUOTES</physicalPath></data><parent>[object]</parent></row><row><id>j1_50</id><text>Quote Received</text><icon>true</icon><li_attr><id>j1_50</id></li_attr><a_attr><href>#</href><id>j1_50_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Quote Received</physicalPath></data><parent>j1_48</parent></row><row><id>j1_49</id><text>Quote Requested</text><icon>true</icon><li_attr><id>j1_49</id></li_attr><a_attr><href>#</href><id>j1_49_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Quote Requested</physicalPath></data><parent>j1_48</parent></row><row><id>j1_51</id><text>Superseded</text><icon>true</icon><li_attr><id>j1_51</id></li_attr><a_attr><href>#</href><id>j1_51_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Superseded</physicalPath></data><parent>j1_48</parent></row><row><id>j1_52</id><text>SAFETY</text><icon>true</icon><li_attr><id>j1_52</id></li_attr><a_attr><href>#</href><id>j1_52_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SAFETY</physicalPath></data><parent>[object]</parent></row><row><id>j1_53</id><text>Site Safety Audits</text><icon>true</icon><li_attr><id>j1_53</id></li_attr><a_attr><href>#</href><id>j1_53_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Site Safety Audits</physicalPath></data><parent>j1_52</parent></row><row><id>j1_54</id><text>SALES ESTIMATING</text><icon>true</icon><li_attr><id>j1_54</id></li_attr><a_attr><href>#</href><id>j1_54_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SALES ESTIMATING</physicalPath></data><parent>[object]</parent></row><row><id>j1_55</id><text>As Constructed Documents</text><icon>true</icon><li_attr><id>j1_55</id></li_attr><a_attr><href>#</href><id>j1_55_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>As Constructed Documents</physicalPath></data><parent>j1_54</parent></row><row><id>j1_57</id><text>Contract Request</text><icon>true</icon><li_attr><id>j1_57</id></li_attr><a_attr><href>#</href><id>j1_57_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Contract Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_58</id><text>Energy Efficiency</text><icon>true</icon><li_attr><id>j1_58</id></li_attr><a_attr><href>#</href><id>j1_58_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Energy Efficiency</physicalPath></data><parent>j1_54</parent></row><row><id>j1_56</id><text>ESTS Compliance Checks</text><icon>true</icon><li_attr><id>j1_56</id></li_attr><a_attr><href>#</href><id>j1_56_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>ESTS Compliance Checks</physicalPath></data><parent>j1_54</parent></row><row><id>j1_59</id><text>ESTS Workup Documents</text><icon>true</icon><li_attr><id>j1_59</id></li_attr><a_attr><href>#</href><id>j1_59_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>ESTS Workup Documents</physicalPath></data><parent>j1_54</parent></row><row><id>j1_72</id><text>Express Request</text><icon>true</icon><li_attr><id>j1_72</id></li_attr><a_attr><href>#</href><id>j1_72_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Express Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_76</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_76</id></li_attr><a_attr><href>#</href><id>j1_76_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_72</parent></row><row><id>j1_77</id><text>House and Land</text><icon>true</icon><li_attr><id>j1_77</id></li_attr><a_attr><href>#</href><id>j1_77_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>House and Land</physicalPath></data><parent>j1_54</parent></row><row><id>j1_60</id><text>Mark-Ups</text><icon>true</icon><li_attr><id>j1_60</id></li_attr><a_attr><href>#</href><id>j1_60_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Mark-Ups</physicalPath></data><parent>j1_54</parent></row><row><id>j1_61</id><text>MSP</text><icon>true</icon><li_attr><id>j1_61</id></li_attr><a_attr><href>#</href><id>j1_61_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>MSP</physicalPath></data><parent>j1_54</parent></row><row><id>j1_63</id><text>Final Plan Request</text><icon>true</icon><li_attr><id>j1_63</id></li_attr><a_attr><href>#</href><id>j1_63_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Final Plan Request</physicalPath></data><parent>j1_61</parent></row><row><id>j1_64</id><text>MSP Costing Workup</text><icon>true</icon><li_attr><id>j1_64</id></li_attr><a_attr><href>#</href><id>j1_64_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>MSP Costing Workup</physicalPath></data><parent>j1_61</parent></row><row><id>j1_62</id><text>Prelim Plan Request</text><icon>true</icon><li_attr><id>j1_62</id></li_attr><a_attr><href>#</href><id>j1_62_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Prelim Plan Request</physicalPath></data><parent>j1_61</parent></row><row><id>j1_69</id><text>Tender 1 Request</text><icon>true</icon><li_attr><id>j1_69</id></li_attr><a_attr><href>#</href><id>j1_69_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tender 1 Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_73</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_73</id></li_attr><a_attr><href>#</href><id>j1_73_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_69</parent></row><row><id>j1_70</id><text>Tender 2 Request</text><icon>true</icon><li_attr><id>j1_70</id></li_attr><a_attr><href>#</href><id>j1_70_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tender 2 Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_74</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_74</id></li_attr><a_attr><href>#</href><id>j1_74_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_70</parent></row><row><id>j1_71</id><text>Tender 3 Request</text><icon>true</icon><li_attr><id>j1_71</id></li_attr><a_attr><href>#</href><id>j1_71_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tender 3 Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_75</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_75</id></li_attr><a_attr><href>#</href><id>j1_75_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_71</parent></row><row><id>j1_78</id><text>SELECTIONS</text><icon>true</icon><li_attr><id>j1_78</id></li_attr><a_attr><href>#</href><id>j1_78_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SELECTIONS</physicalPath></data><parent>[object]</parent></row><row><id>j1_79</id><text>Colours</text><icon>true</icon><li_attr><id>j1_79</id></li_attr><a_attr><href>#</href><id>j1_79_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Colours</physicalPath></data><parent>j1_78</parent></row><row><id>j1_94</id><text>Correspondence</text><icon>true</icon><li_attr><id>j1_94</id></li_attr><a_attr><href>#</href><id>j1_94_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Correspondence</physicalPath></data><parent>j1_79</parent></row><row><id>j1_80</id><text>Layouts</text><icon>true</icon><li_attr><id>j1_80</id></li_attr><a_attr><href>#</href><id>j1_80_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Layouts</physicalPath></data><parent>j1_79</parent></row><row><id>j1_92</id><text>Signed Documents</text><icon>true</icon><li_attr><id>j1_92</id></li_attr><a_attr><href>#</href><id>j1_92_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Documents</physicalPath></data><parent>j1_79</parent></row><row><id>j1_93</id><text>Unsigned Documents</text><icon>true</icon><li_attr><id>j1_93</id></li_attr><a_attr><href>#</href><id>j1_93_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Documents</physicalPath></data><parent>j1_79</parent></row><row><id>j1_85</id><text>Electrical</text><icon>true</icon><li_attr><id>j1_85</id></li_attr><a_attr><href>#</href><id>j1_85_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Electrical</physicalPath></data><parent>j1_78</parent></row><row><id>j1_95</id><text>Signed Documents</text><icon>true</icon><li_attr><id>j1_95</id></li_attr><a_attr><href>#</href><id>j1_95_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Documents</physicalPath></data><parent>j1_85</parent></row><row><id>j1_96</id><text>Unsigned Documents</text><icon>true</icon><li_attr><id>j1_96</id></li_attr><a_attr><href>#</href><id>j1_96_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Documents</physicalPath></data><parent>j1_85</parent></row><row><id>j1_86</id><text>Tiles</text><icon>true</icon><li_attr><id>j1_86</id></li_attr><a_attr><href>#</href><id>j1_86_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tiles</physicalPath></data><parent>j1_78</parent></row><row><id>j1_87</id><text>Window Coverings</text><icon>true</icon><li_attr><id>j1_87</id></li_attr><a_attr><href>#</href><id>j1_87_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Window Coverings</physicalPath></data><parent>j1_78</parent></row><row><id>j1_98</id><text>SERVICE AND WARRANTY</text><icon>true</icon><li_attr><id>j1_98</id></li_attr><a_attr><href>#</href><id>j1_98_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SERVICE AND WARRANTY</physicalPath></data><parent>[object]</parent></row><row><id>j1_101</id><text>HPCS</text><icon>true</icon><li_attr><id>j1_101</id></li_attr><a_attr><href>#</href><id>j1_101_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>HPCS</physicalPath></data><parent>j1_98</parent></row><row><id>j1_100</id><text>Service</text><icon>true</icon><li_attr><id>j1_100</id></li_attr><a_attr><href>#</href><id>j1_100_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Service</physicalPath></data><parent>j1_98</parent></row><row><id>j1_102</id><text>Warranty</text><icon>true</icon><li_attr><id>j1_102</id></li_attr><a_attr><href>#</href><id>j1_102_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Warranty</physicalPath></data><parent>j1_98</parent></row><row><id>j1_103</id><text>VARIATIONS</text><icon>true</icon><li_attr><id>j1_103</id></li_attr><a_attr><href>#</href><id>j1_103_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>VARIATIONS</physicalPath></data><parent>[object]</parent></row><row><id>j1_104</id><text>Signed Variations</text><icon>true</icon><li_attr><id>j1_104</id></li_attr><a_attr><href>#</href><id>j1_104_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Variations</physicalPath></data><parent>j1_103</parent></row><row><id>j1_105</id><text>Unsigned Variations</text><icon>true</icon><li_attr><id>j1_105</id></li_attr><a_attr><href>#</href><id>j1_105_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Variations</physicalPath></data><parent>j1_103</parent></row></root>';
WITH A
AS ( SELECT *
FROM OPENXML(@hdoc, '//*', 2)),
B
AS ( SELECT A.id,
A.parentid,
A.localname,
REPLACE(CAST(A1.[text] AS VARCHAR(255)), '&', '&') AS [text]
FROM A
LEFT JOIN A AS A1 ON A1.parentid = A.id
AND A1.localname = '#text'
WHERE EXISTS (
SELECT *
FROM A AS A2
WHERE A2.id = A.parentid
AND A2.localname IN ( 'row', 'data' )
)),
D
AS ( SELECT DISTINCT id AS RN,
[text] AS id,
(
SELECT [text]
FROM B
WHERE B.parentid = CP.parentid
AND localname = 'text'
) AS [text],
(
SELECT [text]
FROM B
WHERE B.parentid = CP.parentid
AND localname = 'parent'
) AS parent,
(
SELECT B1.[text]
FROM B
INNER JOIN B AS B1 ON B1.parentid = B.id
AND B1.localname = 'physicalPath'
WHERE B.parentid = CP.parentid
AND B.localname = 'data'
) AS physicalPath
FROM B AS CP
WHERE localname = 'id' ),
guids
AS ( SELECT id,
NEWID() AS G
FROM D
WHERE id NOT LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')
AND id <> '[object]' )
SELECT CASE WHEN guids.G IS NULL THEN 'UPDATE'
ELSE 'INSERT'
END AS process,
D.id,
CAST(ISNULL(guids.G, D.id) AS UNIQUEIDENTIFIER) AS gidStructureFolderId,
D.text AS strName,
D.parent,
D.physicalPath,
ROW_NUMBER() OVER ( PARTITION BY parent
ORDER BY RN
) AS intIndexParent
FROM D
LEFT JOIN guids ON guids.id = D.id
WHERE D.id <> '[object]';
May 19, 2020 at 6:32 am
Figured it out - need to pull out the "B" CTE to a temp table: -
DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT,
'<root><row><id>[object]</id><text>Project</text><icon>true</icon><li_attr><id>[object]</id></li_attr><a_attr><href>#</href><id>[object]_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data /><parent>#</parent></row><row><id>46A35569-A2A5-4AAD-8B76-F1F6E7100670</id><text>BUILDING APPROVALS</text><icon>true</icon><li_attr><id>46A35569-A2A5-4AAD-8B76-F1F6E7100670</id></li_attr><a_attr><href>#</href><id>46A35569-A2A5-4AAD-8B76-F1F6E7100670_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>BUILDING APPROVALS</physicalPath><documentCount>0</documentCount><documentTypeCount>0</documentTypeCount></data><parent>[object]</parent></row><row><id>636ED389-4432-48B6-9983-3C3AD7EC23B8</id><text>Amended Approvals</text><icon>true</icon><li_attr><id>636ED389-4432-48B6-9983-3C3AD7EC23B8</id></li_attr><a_attr><href>#</href><id>636ED389-4432-48B6-9983-3C3AD7EC23B8_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Amended Approvals</physicalPath><documentCount>0</documentCount><documentTypeCount>1</documentTypeCount></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_1</id><text>Approved Documents</text><icon>true</icon><li_attr><id>j1_1</id></li_attr><a_attr><href>#</href><id>j1_1_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Approved Documents</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_2</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_2</id></li_attr><a_attr><href>#</href><id>j1_2_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_3</id><text>Covenant</text><icon>true</icon><li_attr><id>j1_3</id></li_attr><a_attr><href>#</href><id>j1_3_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Covenant</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_4</id><text>Knock Down Rebuild Requirements</text><icon>true</icon><li_attr><id>j1_4</id></li_attr><a_attr><href>#</href><id>j1_4_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Knock Down Rebuild Requirements</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_5</id><text>Pre-Lodgement</text><icon>true</icon><li_attr><id>j1_5</id></li_attr><a_attr><href>#</href><id>j1_5_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Pre-Lodgement</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_6</id><text>Request for Information</text><icon>true</icon><li_attr><id>j1_6</id></li_attr><a_attr><href>#</href><id>j1_6_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request for Information</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_7</id><text>Site Specific Reports</text><icon>true</icon><li_attr><id>j1_7</id></li_attr><a_attr><href>#</href><id>j1_7_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Site Specific Reports</physicalPath></data><parent>46A35569-A2A5-4AAD-8B76-F1F6E7100670</parent></row><row><id>j1_9</id><text>CERTIFICATES</text><icon>true</icon><li_attr><id>j1_9</id></li_attr><a_attr><href>#</href><id>j1_9_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CERTIFICATES</physicalPath></data><parent>[object]</parent></row><row><id>j1_10</id><text>CONSTRUCTION</text><icon>true</icon><li_attr><id>j1_10</id></li_attr><a_attr><href>#</href><id>j1_10_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CONSTRUCTION</physicalPath></data><parent>[object]</parent></row><row><id>j1_18</id><text>Customer Obligations</text><icon>true</icon><li_attr><id>j1_18</id></li_attr><a_attr><href>#</href><id>j1_18_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Customer Obligations</physicalPath></data><parent>j1_10</parent></row><row><id>j1_17</id><text>Mandatory Stage Inspections [NSW Only]</text><icon>true</icon><li_attr><id>j1_17</id></li_attr><a_attr><href>#</href><id>j1_17_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Mandatory Stage Inspections [NSW Only]</physicalPath></data><parent>j1_10</parent></row><row><id>j1_16</id><text>Quality assurance Audits</text><icon>true</icon><li_attr><id>j1_16</id></li_attr><a_attr><href>#</href><id>j1_16_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Quality assurance Audits</physicalPath></data><parent>j1_10</parent></row><row><id>j1_15</id><text>Site Safety Audits</text><icon>true</icon><li_attr><id>j1_15</id></li_attr><a_attr><href>#</href><id>j1_15_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Site Safety Audits</physicalPath></data><parent>j1_10</parent></row><row><id>j1_11</id><text>CONTRACT DOCUMENTS</text><icon>true</icon><li_attr><id>j1_11</id></li_attr><a_attr><href>#</href><id>j1_11_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CONTRACT DOCUMENTS</physicalPath></data><parent>[object]</parent></row><row><id>j1_19</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_19</id></li_attr><a_attr><href>#</href><id>j1_19_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>true</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_11</parent></row><row><id>j1_20</id><text>Signed Documents</text><icon>true</icon><li_attr><id>j1_20</id></li_attr><a_attr><href>#</href><id>j1_20_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_21</id><text>Supporting Contract Documents</text><icon>true</icon><li_attr><id>j1_21</id></li_attr><a_attr><href>#</href><id>j1_21_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Supporting Contract Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_22</id><text>To Be Emailed</text><icon>true</icon><li_attr><id>j1_22</id></li_attr><a_attr><href>#</href><id>j1_22_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>To Be Emailed</physicalPath></data><parent>j1_11</parent></row><row><id>j1_23</id><text>Unsigned Documents</text><icon>true</icon><li_attr><id>j1_23</id></li_attr><a_attr><href>#</href><id>j1_23_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_24</id><text>Word Documents</text><icon>true</icon><li_attr><id>j1_24</id></li_attr><a_attr><href>#</href><id>j1_24_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Word Documents</physicalPath></data><parent>j1_11</parent></row><row><id>j1_12</id><text>CORRESPONDENCE</text><icon>true</icon><li_attr><id>j1_12</id></li_attr><a_attr><href>#</href><id>j1_12_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>CORRESPONDENCE</physicalPath></data><parent>[object]</parent></row><row><id>j1_30</id><text>Job Not Proceeding</text><icon>true</icon><li_attr><id>j1_30</id></li_attr><a_attr><href>#</href><id>j1_30_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Job Not Proceeding</physicalPath></data><parent>j1_12</parent></row><row><id>j1_29</id><text>On-Site</text><icon>true</icon><li_attr><id>j1_29</id></li_attr><a_attr><href>#</href><id>j1_29_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>On-Site</physicalPath></data><parent>j1_12</parent></row><row><id>j1_26</id><text>Pre-Site</text><icon>true</icon><li_attr><id>j1_26</id></li_attr><a_attr><href>#</href><id>j1_26_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Pre-Site</physicalPath></data><parent>j1_12</parent></row><row><id>j1_27</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_27</id></li_attr><a_attr><href>#</href><id>j1_27_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_26</parent></row><row><id>j1_13</id><text>DRAFTING</text><icon>true</icon><li_attr><id>j1_13</id></li_attr><a_attr><href>#</href><id>j1_13_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>DRAFTING</physicalPath></data><parent>[object]</parent></row><row><id>j1_33</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_33</id></li_attr><a_attr><href>#</href><id>j1_33_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_13</parent></row><row><id>j1_31</id><text>Contour</text><icon>true</icon><li_attr><id>j1_31</id></li_attr><a_attr><href>#</href><id>j1_31_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Contour</physicalPath></data><parent>j1_13</parent></row><row><id>j1_32</id><text>Engineering Design</text><icon>true</icon><li_attr><id>j1_32</id></li_attr><a_attr><href>#</href><id>j1_32_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Engineering Design</physicalPath></data><parent>j1_13</parent></row><row><id>j1_34</id><text>Plans</text><icon>true</icon><li_attr><id>j1_34</id></li_attr><a_attr><href>#</href><id>j1_34_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Plans</physicalPath></data><parent>j1_13</parent></row><row><id>j1_36</id><text>Tillings</text><icon>true</icon><li_attr><id>j1_36</id></li_attr><a_attr><href>#</href><id>j1_36_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tillings</physicalPath></data><parent>j1_13</parent></row><row><id>j1_35</id><text>Truss Slab PDFs</text><icon>true</icon><li_attr><id>j1_35</id></li_attr><a_attr><href>#</href><id>j1_35_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Truss Slab PDFs</physicalPath></data><parent>j1_13</parent></row><row><id>j1_14</id><text>FINANCE</text><icon>true</icon><li_attr><id>j1_14</id></li_attr><a_attr><href>#</href><id>j1_14_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>FINANCE</physicalPath></data><parent>[object]</parent></row><row><id>j1_37</id><text>Debt Collection</text><icon>true</icon><li_attr><id>j1_37</id></li_attr><a_attr><href>#</href><id>j1_37_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Debt Collection</physicalPath></data><parent>j1_14</parent></row><row><id>j1_38</id><text>OWNER ITEMS</text><icon>true</icon><li_attr><id>j1_38</id></li_attr><a_attr><href>#</href><id>j1_38_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>OWNER ITEMS</physicalPath></data><parent>[object]</parent></row><row><id>j1_39</id><text>Approvals</text><icon>true</icon><li_attr><id>j1_39</id></li_attr><a_attr><href>#</href><id>j1_39_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Approvals</physicalPath></data><parent>j1_38</parent></row><row><id>j1_40</id><text>Denied Requests</text><icon>true</icon><li_attr><id>j1_40</id></li_attr><a_attr><href>#</href><id>j1_40_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Denied Requests</physicalPath></data><parent>j1_38</parent></row><row><id>j1_41</id><text>Specification Documents</text><icon>true</icon><li_attr><id>j1_41</id></li_attr><a_attr><href>#</href><id>j1_41_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Specification Documents</physicalPath></data><parent>j1_38</parent></row><row><id>j1_42</id><text>PRODUCTION ESTIMATING</text><icon>true</icon><li_attr><id>j1_42</id></li_attr><a_attr><href>#</href><id>j1_42_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>PRODUCTION ESTIMATING</physicalPath></data><parent>[object]</parent></row><row><id>j1_46</id><text>Compliance Checks</text><icon>true</icon><li_attr><id>j1_46</id></li_attr><a_attr><href>#</href><id>j1_46_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Compliance Checks</physicalPath></data><parent>j1_42</parent></row><row><id>j1_45</id><text>Correspondence</text><icon>true</icon><li_attr><id>j1_45</id></li_attr><a_attr><href>#</href><id>j1_45_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Correspondence</physicalPath></data><parent>j1_42</parent></row><row><id>j1_43</id><text>Orders</text><icon>true</icon><li_attr><id>j1_43</id></li_attr><a_attr><href>#</href><id>j1_43_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Orders</physicalPath></data><parent>j1_42</parent></row><row><id>j1_47</id><text>Amended Orders</text><icon>true</icon><li_attr><id>j1_47</id></li_attr><a_attr><href>#</href><id>j1_47_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Amended Orders</physicalPath></data><parent>j1_43</parent></row><row><id>j1_44</id><text>Workup Documents</text><icon>true</icon><li_attr><id>j1_44</id></li_attr><a_attr><href>#</href><id>j1_44_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Workup Documents</physicalPath></data><parent>j1_42</parent></row><row><id>j1_48</id><text>QUOTES</text><icon>true</icon><li_attr><id>j1_48</id></li_attr><a_attr><href>#</href><id>j1_48_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>QUOTES</physicalPath></data><parent>[object]</parent></row><row><id>j1_50</id><text>Quote Received</text><icon>true</icon><li_attr><id>j1_50</id></li_attr><a_attr><href>#</href><id>j1_50_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Quote Received</physicalPath></data><parent>j1_48</parent></row><row><id>j1_49</id><text>Quote Requested</text><icon>true</icon><li_attr><id>j1_49</id></li_attr><a_attr><href>#</href><id>j1_49_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Quote Requested</physicalPath></data><parent>j1_48</parent></row><row><id>j1_51</id><text>Superseded</text><icon>true</icon><li_attr><id>j1_51</id></li_attr><a_attr><href>#</href><id>j1_51_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Superseded</physicalPath></data><parent>j1_48</parent></row><row><id>j1_52</id><text>SAFETY</text><icon>true</icon><li_attr><id>j1_52</id></li_attr><a_attr><href>#</href><id>j1_52_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SAFETY</physicalPath></data><parent>[object]</parent></row><row><id>j1_53</id><text>Site Safety Audits</text><icon>true</icon><li_attr><id>j1_53</id></li_attr><a_attr><href>#</href><id>j1_53_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Site Safety Audits</physicalPath></data><parent>j1_52</parent></row><row><id>j1_54</id><text>SALES ESTIMATING</text><icon>true</icon><li_attr><id>j1_54</id></li_attr><a_attr><href>#</href><id>j1_54_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SALES ESTIMATING</physicalPath></data><parent>[object]</parent></row><row><id>j1_55</id><text>As Constructed Documents</text><icon>true</icon><li_attr><id>j1_55</id></li_attr><a_attr><href>#</href><id>j1_55_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>As Constructed Documents</physicalPath></data><parent>j1_54</parent></row><row><id>j1_57</id><text>Contract Request</text><icon>true</icon><li_attr><id>j1_57</id></li_attr><a_attr><href>#</href><id>j1_57_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Contract Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_58</id><text>Energy Efficiency</text><icon>true</icon><li_attr><id>j1_58</id></li_attr><a_attr><href>#</href><id>j1_58_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Energy Efficiency</physicalPath></data><parent>j1_54</parent></row><row><id>j1_56</id><text>ESTS Compliance Checks</text><icon>true</icon><li_attr><id>j1_56</id></li_attr><a_attr><href>#</href><id>j1_56_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>ESTS Compliance Checks</physicalPath></data><parent>j1_54</parent></row><row><id>j1_59</id><text>ESTS Workup Documents</text><icon>true</icon><li_attr><id>j1_59</id></li_attr><a_attr><href>#</href><id>j1_59_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>ESTS Workup Documents</physicalPath></data><parent>j1_54</parent></row><row><id>j1_72</id><text>Express Request</text><icon>true</icon><li_attr><id>j1_72</id></li_attr><a_attr><href>#</href><id>j1_72_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Express Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_76</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_76</id></li_attr><a_attr><href>#</href><id>j1_76_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_72</parent></row><row><id>j1_77</id><text>House and Land</text><icon>true</icon><li_attr><id>j1_77</id></li_attr><a_attr><href>#</href><id>j1_77_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>House and Land</physicalPath></data><parent>j1_54</parent></row><row><id>j1_60</id><text>Mark-Ups</text><icon>true</icon><li_attr><id>j1_60</id></li_attr><a_attr><href>#</href><id>j1_60_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Mark-Ups</physicalPath></data><parent>j1_54</parent></row><row><id>j1_61</id><text>MSP</text><icon>true</icon><li_attr><id>j1_61</id></li_attr><a_attr><href>#</href><id>j1_61_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>MSP</physicalPath></data><parent>j1_54</parent></row><row><id>j1_63</id><text>Final Plan Request</text><icon>true</icon><li_attr><id>j1_63</id></li_attr><a_attr><href>#</href><id>j1_63_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Final Plan Request</physicalPath></data><parent>j1_61</parent></row><row><id>j1_64</id><text>MSP Costing Workup</text><icon>true</icon><li_attr><id>j1_64</id></li_attr><a_attr><href>#</href><id>j1_64_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>MSP Costing Workup</physicalPath></data><parent>j1_61</parent></row><row><id>j1_62</id><text>Prelim Plan Request</text><icon>true</icon><li_attr><id>j1_62</id></li_attr><a_attr><href>#</href><id>j1_62_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Prelim Plan Request</physicalPath></data><parent>j1_61</parent></row><row><id>j1_69</id><text>Tender 1 Request</text><icon>true</icon><li_attr><id>j1_69</id></li_attr><a_attr><href>#</href><id>j1_69_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tender 1 Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_73</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_73</id></li_attr><a_attr><href>#</href><id>j1_73_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_69</parent></row><row><id>j1_70</id><text>Tender 2 Request</text><icon>true</icon><li_attr><id>j1_70</id></li_attr><a_attr><href>#</href><id>j1_70_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tender 2 Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_74</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_74</id></li_attr><a_attr><href>#</href><id>j1_74_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_70</parent></row><row><id>j1_71</id><text>Tender 3 Request</text><icon>true</icon><li_attr><id>j1_71</id></li_attr><a_attr><href>#</href><id>j1_71_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tender 3 Request</physicalPath></data><parent>j1_54</parent></row><row><id>j1_75</id><text>Request Emails</text><icon>true</icon><li_attr><id>j1_75</id></li_attr><a_attr><href>#</href><id>j1_75_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Request Emails</physicalPath></data><parent>j1_71</parent></row><row><id>j1_78</id><text>SELECTIONS</text><icon>true</icon><li_attr><id>j1_78</id></li_attr><a_attr><href>#</href><id>j1_78_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SELECTIONS</physicalPath></data><parent>[object]</parent></row><row><id>j1_79</id><text>Colours</text><icon>true</icon><li_attr><id>j1_79</id></li_attr><a_attr><href>#</href><id>j1_79_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Colours</physicalPath></data><parent>j1_78</parent></row><row><id>j1_94</id><text>Correspondence</text><icon>true</icon><li_attr><id>j1_94</id></li_attr><a_attr><href>#</href><id>j1_94_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Correspondence</physicalPath></data><parent>j1_79</parent></row><row><id>j1_80</id><text>Layouts</text><icon>true</icon><li_attr><id>j1_80</id></li_attr><a_attr><href>#</href><id>j1_80_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Layouts</physicalPath></data><parent>j1_79</parent></row><row><id>j1_92</id><text>Signed Documents</text><icon>true</icon><li_attr><id>j1_92</id></li_attr><a_attr><href>#</href><id>j1_92_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Documents</physicalPath></data><parent>j1_79</parent></row><row><id>j1_93</id><text>Unsigned Documents</text><icon>true</icon><li_attr><id>j1_93</id></li_attr><a_attr><href>#</href><id>j1_93_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Documents</physicalPath></data><parent>j1_79</parent></row><row><id>j1_85</id><text>Electrical</text><icon>true</icon><li_attr><id>j1_85</id></li_attr><a_attr><href>#</href><id>j1_85_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Electrical</physicalPath></data><parent>j1_78</parent></row><row><id>j1_95</id><text>Signed Documents</text><icon>true</icon><li_attr><id>j1_95</id></li_attr><a_attr><href>#</href><id>j1_95_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Documents</physicalPath></data><parent>j1_85</parent></row><row><id>j1_96</id><text>Unsigned Documents</text><icon>true</icon><li_attr><id>j1_96</id></li_attr><a_attr><href>#</href><id>j1_96_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Documents</physicalPath></data><parent>j1_85</parent></row><row><id>j1_86</id><text>Tiles</text><icon>true</icon><li_attr><id>j1_86</id></li_attr><a_attr><href>#</href><id>j1_86_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Tiles</physicalPath></data><parent>j1_78</parent></row><row><id>j1_87</id><text>Window Coverings</text><icon>true</icon><li_attr><id>j1_87</id></li_attr><a_attr><href>#</href><id>j1_87_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Window Coverings</physicalPath></data><parent>j1_78</parent></row><row><id>j1_98</id><text>SERVICE AND WARRANTY</text><icon>true</icon><li_attr><id>j1_98</id></li_attr><a_attr><href>#</href><id>j1_98_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>SERVICE AND WARRANTY</physicalPath></data><parent>[object]</parent></row><row><id>j1_101</id><text>HPCS</text><icon>true</icon><li_attr><id>j1_101</id></li_attr><a_attr><href>#</href><id>j1_101_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>HPCS</physicalPath></data><parent>j1_98</parent></row><row><id>j1_100</id><text>Service</text><icon>true</icon><li_attr><id>j1_100</id></li_attr><a_attr><href>#</href><id>j1_100_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Service</physicalPath></data><parent>j1_98</parent></row><row><id>j1_102</id><text>Warranty</text><icon>true</icon><li_attr><id>j1_102</id></li_attr><a_attr><href>#</href><id>j1_102_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Warranty</physicalPath></data><parent>j1_98</parent></row><row><id>j1_103</id><text>VARIATIONS</text><icon>true</icon><li_attr><id>j1_103</id></li_attr><a_attr><href>#</href><id>j1_103_anchor</id></a_attr><state><loaded>true</loaded><opened>true</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>VARIATIONS</physicalPath></data><parent>[object]</parent></row><row><id>j1_104</id><text>Signed Variations</text><icon>true</icon><li_attr><id>j1_104</id></li_attr><a_attr><href>#</href><id>j1_104_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Signed Variations</physicalPath></data><parent>j1_103</parent></row><row><id>j1_105</id><text>Unsigned Variations</text><icon>true</icon><li_attr><id>j1_105</id></li_attr><a_attr><href>#</href><id>j1_105_anchor</id></a_attr><state><loaded>true</loaded><opened>false</opened><selected>false</selected><disabled>false</disabled></state><data><physicalPath>Unsigned Variations</physicalPath></data><parent>j1_103</parent></row></root>';
WITH A
AS ( SELECT *
FROM OPENXML(@hdoc, '//*', 2))
SELECT A.id,
A.parentid,
A.localname,
REPLACE(CAST(A1.[text] AS VARCHAR(255)), '&', '&') AS [text]
INTO #base
FROM A
LEFT JOIN A AS A1 ON A1.parentid = A.id
AND A1.localname = '#text'
WHERE EXISTS (
SELECT *
FROM A AS A2
WHERE A2.id = A.parentid
AND A2.localname IN ( 'row', 'data' )
);
WITH D
AS ( SELECT DISTINCT id AS RN,
[text] AS id,
(
SELECT [text]
FROM #base AS B
WHERE B.parentid = CP.parentid
AND localname = 'text'
) AS [text],
(
SELECT [text]
FROM #base AS B
WHERE B.parentid = CP.parentid
AND localname = 'parent'
) AS parent,
(
SELECT B1.[text]
FROM #base AS B
INNER JOIN #base AS B1 ON B1.parentid = B.id
AND B1.localname = 'physicalPath'
WHERE B.parentid = CP.parentid
AND B.localname = 'data'
) AS physicalPath
FROM #base AS CP
WHERE localname = 'id' ),
guids
AS ( SELECT id,
NEWID() AS G
FROM D
WHERE id NOT LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')
AND id <> '[object]' )
SELECT CASE WHEN guids.G IS NULL THEN 'UPDATE'
ELSE 'INSERT'
END AS process,
D.id,
CAST(ISNULL(guids.G, D.id) AS UNIQUEIDENTIFIER) AS gidStructureFolderId,
D.text AS strName,
D.parent,
D.physicalPath,
ROW_NUMBER() OVER ( PARTITION BY parent
ORDER BY RN
) AS intIndexParent
FROM D
LEFT JOIN guids ON guids.id = D.id
WHERE D.id <> '[object]';
DROP TABLE #base
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply