April 30, 2013 at 1:55 pm
I’ve experimented with several variations but now I’m ready to call in the troops - I would like to modify this statement:
SELECT
P.SalesPersonID
, P.TerritoryID
, S.Name "Sales/Name"
, S.ModifiedDate "Sales/ModifiedDate"
FROM
Sales.Store AS S INNER JOIN Sales.SalesPerson AS P ON S.SalesPersonID = P.SalesPersonID
WHERE
(S.SalesPersonID = 275)
FOR XML PATH
To output like this:
<row>
<SalesPersonID>275</SalesPersonID>
<TerritoryID>2</TerritoryID>
<Sales>
<Sale>
<Name>Trusted Catalog Store</Name>
<ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate>
</Sale>
<Sale>
<Name>Catalog Store</Name>
<ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate>
</Sale>
</Sales>
</row>
Rather than this:
<row>
<SalesPersonID>275</SalesPersonID>
<TerritoryID>2</TerritoryID>
<Sales>
<Name>Trusted Catalog Store</Name>
<ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate>
</Sales>
</row>
<row>
<SalesPersonID>275</SalesPersonID>
<TerritoryID>2</TerritoryID>
<Sales>
<Name>Catalog Store</Name>
<ModifiedDate>2004-10-13T11:15:07.497</ModifiedDate>
</Sales>
</row>
April 30, 2013 at 5:46 pm
Turn the join into a correlated subquery and use nested FOR XML claused queries, adding the TYPE directive. For example:
declare @Store table (Name nvarchar(21), SalesPersonID smallint, ModifiedDate datetime);
declare @SalesPerson table (SalesPersonID smallint primary key, TerritoryID tinyint);
insert into @Store values ('Trusted Catalog Store', 275, '20041013'), ('Catalog Store', 275, '20041013');
insert into @SalesPerson values (275, 2);
selectp.SalesPersonID "SalesPersonID",
p.TerritoryID "TerritoryID",
(select s2.name "Sale/Name",
s2.ModifiedDate "Sale/ModifiedDate"
from @Store s2
where s2.SalesPersonID = p.SalesPersonID
for xml path(''), type
) "Sales"
from @SalesPerson p
where p.SalesPersonID = 275
for xml path, type
Edit: Fixed typo
April 30, 2013 at 6:07 pm
WITH cteStore (SalesPersonID,TerritoryID)
AS
(
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,2 UNION ALL
SELECT 7,2
),
cteSalesPerson (SalesPersonID,SalesPersonName,ModifiedDate)
AS
(
SELECT 1,'George Washington','2013-03-31' UNION ALL
SELECT 2,'John Adams','2013-02-28' UNION ALL
SELECT 3,'Thomas Jefferson','2013-02-15' UNION ALL
SELECT 4,'James Madison','2013-02-01' UNION ALL
SELECT 5,'James Monroe','2013-01-31' UNION ALL
SELECT 6,'John Q Adams','2013-01-22' UNION ALL
SELECT 1,'George Washington','2013-03-21' UNION ALL
SELECT 7,'Andew Jackson','2013-01-13' UNION ALL
SELECT 3,'Thomas Jefferson','2013-02-05'
)
SELECT
r.strXML
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY p1.SalesPersonID ORDER BY p1.SalesPersonID) AS rn
,(SELECT
(SELECT
s.SalesPersonID
,s.TerritoryID
FROM
cteStore AS s
WHERE
s.SalesPersonID = t.N
FOR XML PATH(''), TYPE)
,(SELECT
(SELECT
p.SalesPersonName AS 'Name'
,p.ModifiedDate
FROM
cteSalesPerson AS p
WHERE
p.SalesPersonID = t.N
ORDER BY
p.SalesPersonID
FOR XML PATH('Sale'), TYPE)
FOR XML PATH('Sales'), TYPE)
FOR XML PATH(''), TYPE)
AS strXML
FROM
cteSalesPerson p1
INNER JOIN
dbo.Tally t
ON t.N = p1.SalesPersonID
) r
WHERE
rn = 1
ORDER BY
rn
May 1, 2013 at 9:48 am
Thank you guys very much.
I'm going to add the export and also the import part so I can just look it up in my briefcas when I forget.
Thanks again!
USE AdventureWorks;
DECLARE @XMLdata XML
SELECT @XMLdata =
(SELECT
p.SalesPersonID "SalesPersonID"
, p.TerritoryID "TerritoryID"
, (SELECT TOP 5
s2.name "Name"
, s2.ModifiedDate "ModifiedDate"
FROM
Sales.Store s2
WHERE
s2.SalesPersonID = p.SalesPersonID
FOR XML PATH('Sale'), TYPE) "Sales"
FROM
Sales.SalesPerson p
WHERE
p.SalesPersonID = 275
FOR XML PATH ('SalesPerson'), TYPE)
--GET RECORDSET
SELECT
r.x.value('SalesPersonID[1]','int')
, r.x.value('TerritoryID[1]','int')
, d.x.value('Name[1]','VARCHAR(100)')
, d.x.value('ModifiedDate[1]','VARCHAR(100)')
FROM
@XMLdata.nodes('(/SalesPerson)') AS r(x) OUTER APPLY r.x.nodes('Sales/Sale') AS d(x)
--GET LEVELS
SELECT
t.c.value('SalesPersonID[1]','int')
, t.c.value('TerritoryID[1]','VARCHAR(255)')
FROM
@XMLdata.nodes('/SalesPerson') t(c);
SELECT
d.x.value('Name[1]','VARCHAR(100)')
, d.x.value('ModifiedDate[1]','VARCHAR(100)')
FROM
@XMLdata.nodes('(/SalesPerson)') AS r(x) OUTER APPLY r.x.nodes('Sales/Sale') AS d(x)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply