FOR XML EXPLICIT problem

This is an example of the sequence I'm after:
<sold_products>
<base_info>
<type_shipment>N</type_shipment>
<origin>
<city>San Diego</city>
<state>CA</state>
</origin>
<destination>
<city>Portland</city>
<state>ME</state>
</destination>
<salesperson_id>1234</salesperson_id>
<nat_acct_nbr>6789</nat_acct_nbr>
</base_info>
.
.
.
</sold_products>
my T_SQL looks like:
SELECT
1 AS Tag,
NULL As Parent,
Shipment.Order_Type AS [special_products!1!order_type!Element],
NULL AS [base_info!2!type_shipment!Element],
NULL AS [origin!3!city!Element],
NULL AS [origin!3!state!Element],
NULL AS [destination!4!city!Element],
NULL AS [destination!4!state!Element],
NULL AS [base_info!5!salesperson_id!Element],
NULL AS [base_info!5!nat_acct_nbr!Element]
FROM
Shipment
WHERE Shipment.shipment_id = 140915
UNION ALL
SELECT
2,
1,
NULL,
Shipment_Detail.Type_Shipment,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM
Shipment
LEFT JOIN
Shipment_Detail ON Shipment.Shipment_ID = Shipment_Detail.Shipment_ID
WHERE Shipment.shipment_id = 140915
UNION ALL
-- get origin info
UNION ALL
-- get destination info
UNION ALL
-- get origin info
SELECT
5,
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Shipment_Sales.Salesperson_ID,
Shipment_Sales.nat_acct_nbr
FROM
Shipment
LEFT JOIN
Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
WHERE Shipment.shipment_id = 140915
My problem is getting the <salesperson_id> and <nat_acct_nbr> elements
within the same
<base_info> element as <type_shipment>. As it is wriiten above and by
setting all base_info tags = 2,
I get:
<base_info>
<type_shipment>N</type_shipment>
<origin>
<city>San Diego</city>
<state>CA</state>
</origin>
<destination>
<city>Portland</city>
<state>ME</state>
</destination>
</base_info>
<base_info>
<salesperson_id>1234</salesperson_id>
<nat_acct_nbr>6789</nat_acct_nbr>
</base_info>
How can I do this?
Thank you in advance for your time.The problem is that each row in your query appears as a separate
base_info tag. Since you get the shipment detail column in the first
row and the salesperson_id and nat_acct_nbr in another row, they appear
in different tags. Thus if you want to include the salesperson_id tag
and nat_acct_nbr tag in the same base_info tag, you need to try this:
SELECT
1 AS Tag,
NULL As Parent,
Shipment.Order_Type AS [special_products!1!order_type!Element],
NULL AS [base_info!2!type_shipment!Element],
NULL AS [origin!3!city!Element],
NULL AS [origin!3!state!Element],
NULL AS [destination!4!city!Element],
NULL AS [destination!4!state!Element],
NULL AS [base_info!2!salesperson_id!Element],
NULL AS [base_info!2!nat_acct_nbr!Element]
FROM
Shipment
WHERE Shipment.shipment_id = 140915
UNION ALL
SELECT
2,
1,
NULL,
Shipment_Detail.Type_Shipment,
NULL,
NULL,
NULL,
NULL,
Shipment_Sales.Salesperson_ID,
Shipment_Sales.nat_acct_nbr
FROM
Shipment
LEFT JOIN
Shipment_Detail ON Shipment.Shipment_ID =
Shipment_Detail.Shipment_ID
LEFT JOIN
Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
WHERE Shipment.shipment_id = 140915
UNION ALL
-- get origin info
UNION ALL
-- get destination info
UNION ALL
-- get origin info|||Thank you for your response. If I do it this way, salesperson_id and
nat_acct_nbr immediately follow type_shipment in the xml stream instead of
following destination. This is invalid because the schema requires the
sequence to be as it is in my posting. Thanks again.
<kniemczak@.gmail.com> wrote in message
news:1133388828.086137.136010@.g43g2000cwa.googlegroups.com...
> The problem is that each row in your query appears as a separate
> base_info tag. Since you get the shipment detail column in the first
> row and the salesperson_id and nat_acct_nbr in another row, they appear
> in different tags. Thus if you want to include the salesperson_id tag
> and nat_acct_nbr tag in the same base_info tag, you need to try this:
> SELECT
> 1 AS Tag,
> NULL As Parent,
> Shipment.Order_Type AS [special_products!1!order_type!Element],
> NULL AS [base_info!2!type_shipment!Element],
> NULL AS [origin!3!city!Element],
> NULL AS [origin!3!state!Element],
> NULL AS [destination!4!city!Element],
> NULL AS [destination!4!state!Element],
> NULL AS [base_info!2!salesperson_id!Element],
> NULL AS [base_info!2!nat_acct_nbr!Element]
> FROM
> Shipment
> WHERE Shipment.shipment_id = 140915
>
> UNION ALL
>
> SELECT
> 2,
> 1,
> NULL,
> Shipment_Detail.Type_Shipment,
> NULL,
> NULL,
> NULL,
> NULL,
> Shipment_Sales.Salesperson_ID,
> Shipment_Sales.nat_acct_nbr
> FROM
> Shipment
> LEFT JOIN
> Shipment_Detail ON Shipment.Shipment_ID =
> Shipment_Detail.Shipment_ID
> LEFT JOIN
> Shipment_Sales ON Shipment.Shipment_ID = Shipment_Sales.Shipment_ID
> WHERE Shipment.shipment_id = 140915
>
> UNION ALL
>
> -- get origin info
>
> UNION ALL
>
> -- get destination info
>
> UNION ALL
>
> -- get origin info
>|||Ok this reply will be short, but if you want me to elaborate further,
just ask. Try using the shipment ID to group the XML tags. Use your
original query, but add a hidden value for shipment id so that tags 2
and 5 both are associated with shipment id X.|||Also, if you have SQL Server 2005, you may want to use FOR XML PATH instead.
Best regards
Michael
<kniemczak@.gmail.com> wrote in message
news:1133500921.199025.127140@.g43g2000cwa.googlegroups.com...
> Ok this reply will be short, but if you want me to elaborate further,
> just ask. Try using the shipment ID to group the XML tags. Use your
> original query, but add a hidden value for shipment id so that tags 2
> and 5 both are associated with shipment id X.
>