SQL Server TSQL XML Add a child note within an artificial node in explicit XML Path

gmol1639  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(69)

I'm trying to create an XML in SQL Server that contains one order. One order may have multiple shipments based on certain criteria. There are several nodes so i was able to get the XML built using artificial nodes with the help of djangojazz (TSQL XML - Add attribute to parent node). Everything is coming together well except for one piece. Within the XML there is an "Items" Node. There could be several items within a shipment, but with the way I am using the artificial nodes, i can't quite successfully get the layers of individual items within the Items node correctly. The code is creating a duplicate Shipment ID Node with each unique Item. I have surrounded the code/XML in question with *'s below.

My Select statement:

SELECT 

        --Shipment
        --Enterprise
        'Pending' AS 'Status',
        orm.cust_po_number AS 'Enterprise/@customerAcctNum',
        orm.customer_name AS 'Enterprise/@name',
        '' AS 'Enterprise/*',
        --Ref Numbers
        'ShipmentID' AS 'ReferenceNumbers/ReferenceNumber/@type',
        'true' AS 'ReferenceNumbers/ReferenceNumber/@isPrimary',
        ord.order_number + '.' + CAST(RIGHT('0000000000'+CAST(ord.shipment_sequence AS VARCHAR(3)),10) AS VARCHAR) AS 'ReferenceNumbers/ReferenceNumber/*',
        'QuoteNumber' AS 'ReferenceNumbers/ReferenceNumberTwo/@type',
        'false' AS 'ReferenceNumbers/ReferenceNumberTwo/@isPrimary',
        orm.quote_number AS 'ReferenceNumbers/ReferenceNumberTwo/*',
        --Ship Comments
        --'SpecialInstructions' AS 'Comments/Comment/@type',
        '' AS 'Comments/Comment/*',
        --Equipment List    
        '' AS 'EquipmentList/EquipmentCode/@desc',
        '' AS 'EquipmentList/EquipmentCode/@qty',
        '' AS 'EquipmentList/EquipmentCode/*',
        --Dates
        'earliest' AS 'Dates/Pickup/Date/@type',
        FORMAT(ord.schedule_ship, 'yyyy/MM/dd HH:mm', 'en-US') AS 'Dates/Pickup/Date/*',
        'latest' AS 'Dates/Pickup/DateTwo/@type',
        FORMAT(ord.schedule_ship, 'yyyy/MM/dd HH:mm', 'en-US') AS 'Dates/Pickup/DateTwo/*',
        'earliest' AS 'Dates/Drop/Date/@type',
        FORMAT(ord.schedule_ship, 'yyyy/MM/dd HH:mm', 'en-US') AS 'Dates/Drop/Date/*',
        'latest' AS 'Dates/Drop/DateTwo/@type',
        FORMAT(ord.schedule_ship, 'yyyy/MM/dd HH:mm', 'en-US') AS 'Dates/Drop/DateTwo/*',
        --Shipper
        'false' AS 'Shipper/Address/@isResidential',
        '' AS 'Shipper/Address/Alias',
        ord.ship_from_name AS 'Shipper/Address/Name',
        ord.ship_from_addr1 AS 'Shipper/Address/AddrLine1',
        ord.ship_from_addr2 AS 'Shipper/Address/AddrLine2',
        ord.ship_from_city AS 'Shipper/Address/City',
        ord.ship_from_state AS 'Shipper/Address/StateProvince',
        ord.ship_from_zip AS 'Shipper/Address/PostalCode',
        ord.ship_from_country_code AS 'Shipper/Address/CountryCode',
        '' AS 'Shipper/Address/Contacts/Contact/Name/*',
        '' AS 'Shipper/Address/Contacts/Contact/ContactMethods/ContactMethod/@type',
        '' AS 'Shipper/Address/Contacts/Contact/ContactMethods/ContactMethod/@sequenceNum',
        '' AS 'Shipper/Address/Contacts/Contact/ContactMethods/ContactMethod/*',
        '' AS 'Shipper/Address/Comments',
        --Consignee
        CASE 
            WHEN ord.ship_to_is_residential = 'Y'
            THEN 'true'
            ELSE 'false'
        END AS 'Consignee/Address/@isResidential',
        '' AS 'Consignee/Address/Alias',
        orm.ship_to_name AS 'Consignee/Address/Name',
        orm.ship_to_addr1 AS 'Consignee/Address/AddrLine1',
        orm.ship_to_addr2 AS 'Consignee/Address/AddrLine2',
        orm.ship_to_city AS 'Consignee/Address/City',
        orm.ship_to_state AS 'Consignee/Address/StateProvince',
        orm.ship_to_zip AS 'Consignee/Address/PostalCode',
        orm.ship_to_country_code AS 'Consignee/Address/CountryCode',
        '' AS 'Consignee/Address/Contacts/Contact/Name/*',
        '' AS 'Consignee/Address/Contacts/Contact/ContactMethods/ContactMethod/@type',
        '' AS 'Consignee/Address/Contacts/Contact/ContactMethods/ContactMethod/@sequenceNum',
        '' AS 'Consignee/Address/Contacts/Contact/ContactMethods/ContactMethod/*',
        '' AS 'Consignee/Address/Comments',
        --Handling Units
        'false' AS 'HandlingUnits/HandlingUnit/@stackable',
        'Pallet' AS 'HandlingUnits/HandlingUnit/Quantity/@units',
        CAST(SUM(ord.qty) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Quantity/*',
        'lb' AS 'HandlingUnits/HandlingUnit/Weight/@units',
        CAST(SUM(ord.extended_weight) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Weight/*',
        CAST(SUM(ord.container_height) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Dimensions/@height',
        'in' AS 'HandlingUnits/HandlingUnit/Dimensions/@units',
        CAST(SUM(ord.container_width) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Dimensions/@width',
        CAST(SUM(ord.container_length) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Dimensions/@length',

        --********************************
        '' AS 'HandlingUnits/HandlingUnit/Items/*',
        ord.freight_class AS 'HandlingUnits/HandlingUnit/Items/Item/@FreightClass',
        '1' AS 'HandlingUnits/HandlingUnit/Items/Item/@sequence',
        'lb' AS 'HandlingUnits/HandlingUnit/Items/Item/Weight/@units',
        CAST(SUM(ord.extended_weight) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Items/Item/Weight/*',
        CAST(SUM(ord.container_height) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Items/Item/Dimensions/@height',
        'in' AS 'HandlingUnits/HandlingUnit/Items/Item/Dimensions/@units',
        CAST(SUM(ord.container_width) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Items/Item/Dimensions/@width',
        CAST(SUM(ord.container_length) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Items/Item/Dimensions/@length',
        '' AS 'HandlingUnits/HandlingUnit/Items/Item/Quantity/@units',
        CAST(SUM(ord.qty) AS NUMERIC(4,1)) AS 'HandlingUnits/HandlingUnit/Items/Item/Quantity/*',
        '' AS 'HandlingUnits/HandlingUnit/Items/Item/NMFC/*',
        'false' AS 'HandlingUnits/HandlingUnit/Items/Item/HazardousMaterial/*',
        --******************************** 

        --Payment   
        CASE 
            WHEN ord.ship_from_addr1 = @v_vchWengerAdd
                    AND ord.ship_from_zip = @v_vchWengerZip
            THEN 'Prepaid'
            ELSE 'Third Party'
        END AS 'Payment/Method',
        CASE 
            WHEN ord.ship_from_addr1 = @v_vchWengerAdd
                    AND ord.ship_from_zip = @v_vchWengerZip
            THEN 'false'
            ELSE 'true'
        END AS 'Payment/BillTo/@thirdParty',
        CASE 
            WHEN ord.ship_to_is_residential = 'Y'
            THEN 'true'
            ELSE 'false'
        END AS 'Payment/BillTo/Address/@isresidential',
        '' AS 'Payment/BillTo/Address/Alias',
        orm.bill_to_name AS 'Payment/BillTo/Address/Name',
        orm.bill_to_addr1 AS 'Payment/BillTo/Address/AddrLine1',
        orm.bill_to_addr2 AS 'Payment/BillTo/Address/AddrLine2',
        orm.bill_to_city AS 'Payment/BillTo/Address/City',
        orm.bill_to_state AS 'Payment/BillTo/Address/StateProvince',
        orm.bill_to_zip AS 'Payment/BillTo/Address/PostalCode',
        orm.bill_to_addr1 AS 'Payment/BillTo/Address/CountryCode',
        '' AS 'Payment/BillTo/Address/Contacts/Contact/Name/*',
        '' AS 'Payment/BillTo/Address/Contacts/Contact/ContactMethods/ContactMethod/@type',
        '' AS 'Payment/BillTo/Address/Contacts/Contact/ContactMethods/ContactMethod/@sequenceNum',
        '' AS 'Payment/BillTo/Address/Contacts/Contact/ContactMethods/ContactMethod/*'

        FROM t_order_detail ord WITH (NOLOCK)
    INNER JOIN t_order orm WITH (NOLOCK) 
        ON ord.wh_id = orm.wh_id
        AND ord.order_number = orm.order_number
    WHERE ord.wh_id = @in_vchWhID
        AND ord.order_number = @in_vchOrderNumber

    GROUP BY ord.shipment_sequence, ord.order_number, orm.quote_number, orm.cust_po_number, orm.customer_name, ord.schedule_ship,   ord.ship_from_name,
                ord.ship_from_addr1, ord.ship_from_addr2, ord.ship_from_city, ord.ship_from_state,  ord.ship_from_zip, ord.ship_from_country_code,
                ord.ship_to_is_residential, orm.ship_to_name, orm.ship_to_addr1, orm.ship_to_addr2, orm.ship_to_city, orm.ship_to_state,
                orm.ship_to_zip, orm.ship_to_country_code, ord.freight_class, orm.bill_to_name, orm.bill_to_addr1, orm.bill_to_addr2,
                orm.bill_to_city,orm.bill_to_state, orm.bill_to_zip

    FOR XML PATH('Shipment'), TYPE

Result:

<Shipment>
      <Status>Pending</Status>
      <Enterprise customerAcctNum="SIGNED QUOTE" name="CHRIS STORJOHANN"></Enterprise>
      <ReferenceNumbers>
        <ReferenceNumber type="ShipmentID" isPrimary="true">1564301.0000000043</ReferenceNumber>
        <ReferenceNumberTwo type="QuoteNumber" isPrimary="false" />
      </ReferenceNumbers>
      <Comments>
        <Comment></Comment>
      </Comments>
      <EquipmentList>
        <EquipmentCode desc="" qty=""></EquipmentCode>
      </EquipmentList>
      <Dates>
        <Pickup>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Pickup>
        <Drop>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Drop>
      </Dates>
      <Shipper>
        <Address isResidential="false">
          <Alias></Alias>
          <Name>WENGER CORPORATION</Name>
          <AddrLine1>555 PARK DR</AddrLine1>
          <City>OWATONNA</City>
          <StateProvince>MN</StateProvince>
          <PostalCode>55060-4980</PostalCode>
          <CountryCode>USA</CountryCode>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Shipper>
      <Consignee>
        <Address isResidential="false">
          <Alias></Alias>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Consignee>
      <HandlingUnits>
        <HandlingUnit stackable="false">
          <Quantity units="Pallet">83.0</Quantity>
          <Weight units="lb">153.9</Weight>
          <Dimensions height="75.6" units="in" width="31.1" length="72.6" />
          ***************************************************
            <Items>
            <Item FreightClass="065" sequence="1">
              <Weight units="lb">153.9</Weight>
              <Dimensions height="75.6" units="in" width="31.1" length="72.6" />
              <Quantity units="">83.0</Quantity>
              <NMFC></NMFC>
              <HazardousMaterial>false</HazardousMaterial>
            </Item>
          </Items>
        *****************************************************
        </HandlingUnit>
      </HandlingUnits>
      <Payment>
        <Method>Prepaid</Method>
        <BillTo thirdParty="false">
          <Address isresidential="false">
            <Alias></Alias>
            <Name>Valdosta Lowndes Conf. Ctr.</Name>
            <AddrLine1>1 Meeting Pl</AddrLine1>
            <City>Valdosta</City>
            <StateProvince>GA</StateProvince>
            <PostalCode>316017710</PostalCode>
            <CountryCode>1 Meeting Pl</CountryCode>
            <Contacts>
              <Contact>
                <Name></Name>
                <ContactMethods>
                  <ContactMethod type="" sequenceNum=""></ContactMethod>
                </ContactMethods>
              </Contact>
            </Contacts>
          </Address>
        </BillTo>
      </Payment>
    </Shipment>
    <Shipment>
      <Status>Pending</Status>
      <Enterprise customerAcctNum="SIGNED QUOTE" name="CHRIS STORJOHANN"></Enterprise>
      <ReferenceNumbers>
        <ReferenceNumber type="ShipmentID" isPrimary="true">1564301.0000000043</ReferenceNumber>
        <ReferenceNumberTwo type="QuoteNumber" isPrimary="false" />
      </ReferenceNumbers>
      <Comments>
        <Comment></Comment>
      </Comments>
      <EquipmentList>
        <EquipmentCode desc="" qty=""></EquipmentCode>
      </EquipmentList>
      <Dates>
        <Pickup>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Pickup>
        <Drop>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Drop>
      </Dates>
      <Shipper>
        <Address isResidential="false">
          <Alias></Alias>
          <Name>WENGER CORPORATION</Name>
          <AddrLine1>555 PARK DR</AddrLine1>
          <City>OWATONNA</City>
          <StateProvince>MN</StateProvince>
          <PostalCode>55060-4980</PostalCode>
          <CountryCode>USA</CountryCode>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Shipper>
      <Consignee>
        <Address isResidential="false">
          <Alias></Alias>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Consignee>
      <HandlingUnits>
        <HandlingUnit stackable="false">
          <Quantity units="Pallet">1.0</Quantity>
          <Weight units="lb">85.0</Weight>
          <Dimensions height="12.1" units="in" width="5.7" length="10.6" />
            **************************************************************
          <Items>
            <Item FreightClass="085" sequence="1">
              <Weight units="lb">85.0</Weight>
              <Dimensions height="12.1" units="in" width="5.7" length="10.6" />
              <Quantity units="">1.0</Quantity>
              <NMFC></NMFC>
              <HazardousMaterial>false</HazardousMaterial>
            </Item>
          </Items>
            **************************************************************
        </HandlingUnit>
      </HandlingUnits>
      <Payment>
        <Method>Prepaid</Method>
        <BillTo thirdParty="false">
          <Address isresidential="false">
            <Alias></Alias>
            <Name>Valdosta Lowndes Conf. Ctr.</Name>
            <AddrLine1>1 Meeting Pl</AddrLine1>
            <City>Valdosta</City>
            <StateProvince>GA</StateProvince>
            <PostalCode>316017710</PostalCode>
            <CountryCode>1 Meeting Pl</CountryCode>
            <Contacts>
              <Contact>
                <Name></Name>
                <ContactMethods>
                  <ContactMethod type="" sequenceNum=""></ContactMethod>
                </ContactMethods>
              </Contact>
            </Contacts>
          </Address>
        </BillTo>
      </Payment>
    </Shipment>
    <Shipment>
      <Status>Pending</Status>
      <Enterprise customerAcctNum="SIGNED QUOTE" name="CHRIS STORJOHANN"></Enterprise>
      <ReferenceNumbers>
        <ReferenceNumber type="ShipmentID" isPrimary="true">1564301.0000000044</ReferenceNumber>
        <ReferenceNumberTwo type="QuoteNumber" isPrimary="false" />
      </ReferenceNumbers>
      <Comments>
        <Comment></Comment>
      </Comments>
      <EquipmentList>
        <EquipmentCode desc="" qty=""></EquipmentCode>
      </EquipmentList>
      <Dates>
        <Pickup>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Pickup>
        <Drop>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Drop>
      </Dates>
      <Shipper>
        <Address isResidential="false">
          <Alias></Alias>
          <Name>CHARTER BUILDERS, INC.</Name>
          <AddrLine1>STE 700</AddrLine1>
          <AddrLine2>1505 LYNDON B JOHNSON FWY</AddrLine2>
          <City>DALLAS</City>
          <StateProvince>TX</StateProvince>
          <PostalCode>75234-6065</PostalCode>
          <CountryCode>USA</CountryCode>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Shipper>
      <Consignee>
        <Address isResidential="false">
          <Alias></Alias>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Consignee>
      <HandlingUnits>
        <HandlingUnit stackable="false">
          <Quantity units="Pallet">54.0</Quantity>
          <Weight units="lb">49.6</Weight>
          <Dimensions height="21.3" units="in" width="10.9" length="36.1" />
          <Items>
            <Item FreightClass="065" sequence="1">
              <Weight units="lb">49.6</Weight>
              <Dimensions height="21.3" units="in" width="10.9" length="36.1" />
              <Quantity units="">54.0</Quantity>
              <NMFC></NMFC>
              <HazardousMaterial>false</HazardousMaterial>
            </Item>
          </Items>
        </HandlingUnit>
      </HandlingUnits>
      <Payment>
        <Method>Third Party</Method>
        <BillTo thirdParty="true">
          <Address isresidential="false">
            <Alias></Alias>
            <Name>Valdosta Lowndes Conf. Ctr.</Name>
            <AddrLine1>1 Meeting Pl</AddrLine1>
            <City>Valdosta</City>
            <StateProvince>GA</StateProvince>
            <PostalCode>316017710</PostalCode>
            <CountryCode>1 Meeting Pl</CountryCode>
            <Contacts>
              <Contact>
                <Name></Name>
                <ContactMethods>
                  <ContactMethod type="" sequenceNum=""></ContactMethod>
                </ContactMethods>
              </Contact>
            </Contacts>
          </Address>
        </BillTo>
      </Payment>
    </Shipment>

Desired Result:

<Shipment>
      <Status>Pending</Status>
      <Enterprise customerAcctNum="SIGNED QUOTE" name="CHRIS STORJOHANN"></Enterprise>
      <ReferenceNumbers>
        <ReferenceNumber type="ShipmentID" isPrimary="true">1564301.0000000043</ReferenceNumber>
        <ReferenceNumberTwo type="QuoteNumber" isPrimary="false" />
      </ReferenceNumbers>
      <Comments>
        <Comment></Comment>
      </Comments>
      <EquipmentList>
        <EquipmentCode desc="" qty=""></EquipmentCode>
      </EquipmentList>
      <Dates>
        <Pickup>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Pickup>
        <Drop>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Drop>
      </Dates>
      <Shipper>
        <Address isResidential="false">
          <Alias></Alias>
          <Name>WENGER CORPORATION</Name>
          <AddrLine1>555 PARK DR</AddrLine1>
          <City>OWATONNA</City>
          <StateProvince>MN</StateProvince>
          <PostalCode>55060-4980</PostalCode>
          <CountryCode>USA</CountryCode>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Shipper>
      <Consignee>
        <Address isResidential="false">
          <Alias></Alias>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Consignee>
      <HandlingUnits>
        <HandlingUnit stackable="false">
          <Quantity units="Pallet">83.0</Quantity>
          <Weight units="lb">153.9</Weight>
          <Dimensions height="75.6" units="in" width="31.1" length="72.6" />
          ****************************************************
            <Items>
            <Item FreightClass="065" sequence="1">
              <Weight units="lb">153.9</Weight>
              <Dimensions height="75.6" units="in" width="31.1" length="72.6" />
              <Quantity units="">83.0</Quantity>
              <NMFC></NMFC>
              <HazardousMaterial>false</HazardousMaterial>
            </Item>
            <Item FreightClass="085" sequence="1">
             <Weight units="lb">85.0</Weight>
             <Dimensions height="12.1" units="in" width="5.7" length="10.6" />
             <Quantity units="">1.0</Quantity>
             <NMFC></NMFC>
             <HazardousMaterial>false</HazardousMaterial>
            </Item>   
          </Items>
        ******************************************************
        </HandlingUnit>
      </HandlingUnits>
      <Payment>
        <Method>Prepaid</Method>
        <BillTo thirdParty="false">
          <Address isresidential="false">
            <Alias></Alias>
            <Name>Valdosta Lowndes Conf. Ctr.</Name>
            <AddrLine1>1 Meeting Pl</AddrLine1>
            <City>Valdosta</City>
            <StateProvince>GA</StateProvince>
            <PostalCode>316017710</PostalCode>
            <CountryCode>1 Meeting Pl</CountryCode>
            <Contacts>
              <Contact>
                <Name></Name>
                <ContactMethods>
                  <ContactMethod type="" sequenceNum=""></ContactMethod>
                </ContactMethods>
              </Contact>
            </Contacts>
          </Address>
        </BillTo>
      </Payment>
    </Shipment>
    <Shipment>
      <Status>Pending</Status>
      <Enterprise customerAcctNum="SIGNED QUOTE" name="CHRIS STORJOHANN"></Enterprise>
      <ReferenceNumbers>
        <ReferenceNumber type="ShipmentID" isPrimary="true">1564301.0000000044</ReferenceNumber>
        <ReferenceNumberTwo type="QuoteNumber" isPrimary="false" />
      </ReferenceNumbers>
      <Comments>
        <Comment></Comment>
      </Comments>
      <EquipmentList>
        <EquipmentCode desc="" qty=""></EquipmentCode>
      </EquipmentList>
      <Dates>
        <Pickup>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Pickup>
        <Drop>
          <Date type="earliest">2014/02/26 00:00</Date>
          <DateTwo type="latest">2014/02/26 00:00</DateTwo>
        </Drop>
      </Dates>
      <Shipper>
        <Address isResidential="false">
          <Alias></Alias>
          <Name>CHARTER BUILDERS, INC.</Name>
          <AddrLine1>STE 700</AddrLine1>
          <AddrLine2>1505 LYNDON B JOHNSON FWY</AddrLine2>
          <City>DALLAS</City>
          <StateProvince>TX</StateProvince>
          <PostalCode>75234-6065</PostalCode>
          <CountryCode>USA</CountryCode>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Shipper>
      <Consignee>
        <Address isResidential="false">
          <Alias></Alias>
          <Contacts>
            <Contact>
              <Name></Name>
              <ContactMethods>
                <ContactMethod type="" sequenceNum=""></ContactMethod>
              </ContactMethods>
            </Contact>
          </Contacts>
          <Comments></Comments>
        </Address>
      </Consignee>
      <HandlingUnits>
        <HandlingUnit stackable="false">
          <Quantity units="Pallet">54.0</Quantity>
          <Weight units="lb">49.6</Weight>
          <Dimensions height="21.3" units="in" width="10.9" length="36.1" />
          <Items>
            <Item FreightClass="065" sequence="1">
              <Weight units="lb">49.6</Weight>
              <Dimensions height="21.3" units="in" width="10.9" length="36.1" />
              <Quantity units="">54.0</Quantity>
              <NMFC></NMFC>
              <HazardousMaterial>false</HazardousMaterial>
            </Item>
          </Items>
        </HandlingUnit>
      </HandlingUnits>
      <Payment>
        <Method>Third Party</Method>
        <BillTo thirdParty="true">
          <Address isresidential="false">
            <Alias></Alias>
            <Name>Valdosta Lowndes Conf. Ctr.</Name>
            <AddrLine1>1 Meeting Pl</AddrLine1>
            <City>Valdosta</City>
            <StateProvince>GA</StateProvince>
            <PostalCode>316017710</PostalCode>
            <CountryCode>1 Meeting Pl</CountryCode>
            <Contacts>
              <Contact>
                <Name></Name>
                <ContactMethods>
                  <ContactMethod type="" sequenceNum=""></ContactMethod>
                </ContactMethods>
              </Contact>
            </Contacts>
          </Address>
        </BillTo>
      </Payment>
    </Shipment>

As you can see, the code is creating a separate shipment block for the second item in the same shipment ID. I need there to be one Shipment ID of "64301.0000000043" and all unique items listed within the "Items" Node. I tried to do a subquery where the Item list in several different ways, but the results were always incorrect.

sql-server

1条答案

按热度按时间
mnemlml8

mnemlml81#

You can get a nested 1:n relation with a sub-select. Your attempt using a join would multiply the parent rows in a normal result set. Therefore you'll see your content within XML multiplied too.

Without sample data it is impossible to provide a secure solution, but you might try to

  • take away the JOIN and
  • use a nested FOR XML sub-select

Check this:

SELECT [...ManyColumns...]

      ,(
          SELECT        
            ord.freight_class AS '@FreightClass',
            '1' AS '@sequence',
            'lb' AS 'Weight/@units',
            CAST(SUM(ord.extended_weight) AS NUMERIC(4,1)) AS 'Weight/*',
            CAST(SUM(ord.container_height) AS NUMERIC(4,1)) AS 'Dimensions/@height',
            'in' AS 'Dimensions/@units',
            CAST(SUM(ord.container_width) AS NUMERIC(4,1)) AS 'Dimensions/@width',
            CAST(SUM(ord.container_length) AS NUMERIC(4,1)) AS 'Dimensions/@length',
            '' AS 'Quantity/@units',
            CAST(SUM(ord.qty) AS NUMERIC(4,1)) AS 'Quantity/*',
            '' AS 'NMFC/*',
            'false' AS 'HazardousMaterial/*'
          FROM t_order_detail ord
          WHERE ord.wh_id=orm.wh_id
            AND ord.order_number=orm.order_number
            AND ord.wh_id = @in_vchWhID
            AND ord.order_number = @in_vchOrderNumber      
          FOR XML PATH('Item'),ROOT('Items'),TYPE
      ) AS 'HandlingUnits/HandlingUnit/*'

      ,[...MoreColumns...]

FROM t_order orm
[...]

You'll get into troubles assumeably due to the later GROUP BY ... Don't know, what you are really trying to achieve.

One solution might be, to place your query with the GROUP BY , but without the nested items in a CTE as a normal derived table. With this result set you can create your FOR XML and add the nested items...

赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com