Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Output question Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 1:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 94, Visits: 208
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>



Post #1448225
Posted Tuesday, April 30, 2013 5:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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);

select p.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
Post #1448277
Posted Tuesday, April 30, 2013 6:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721

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




 
Post #1448278
Posted Wednesday, May 1, 2013 9:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 94, Visits: 208
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)



Post #1448503
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse