September 29, 2010 at 1:52 pm
I have been struggling the past few days in getting this working. I'm trying to create an XML fragment for each row of data in my dataset. However, the only success that I've had is that I create an XML fragment, but it is for the ENTIRE dataset (every row/column).
How can I structure the SQL such that it creates and outputs and XML fragment for each row individually?
Here's the sample data:
CREATE TABLE Students (
FirstName nvarchar(50),
LastName nvarchar(50),
DisplayName nvarchar(100)
)
INSERT INTO Students
SELECT 'Jim' as FirstName, 'Bob' as LastName, 'Jim Bob' as DisplayName
UNION
SELECT 'John', 'Doe', 'John Doe'
UNION
SELECT 'Jane', 'Doe', 'Jane Doe'
UNION
SELECT 'Yuri', 'Tao', 'Yuri Tao'
But, My XML comes out as a singular XML row in SQL Server. I want an XML value to be generated for each row. This is my XML code below:
select * from Students
FOR XML RAW
This is how I want my table to turn out:
CREATE TABLE StudentsXML (
FirstName nvarchar(50),
LastName nvarchar(50),
DisplayName nvarchar(100),
XMLData XML
)
INSERT INTO StudentsXML
SELECT 'Jim' as FirstName, 'Bob' as LastName, 'Jim Bob' as DisplayName, '<row FirstName="Jane" LastName="Doe" DisplayName="Jane Doe" />' as XMLData
UNION
SELECT 'John', 'Doe', 'John Doe', '<row FirstName="Jim" LastName="Bob" DisplayName="Jim Bob" />'
UNION
SELECT 'Jane', 'Doe', 'Jane Doe', '<row FirstName="John" LastName="Doe" DisplayName="John Doe" />'
UNION
SELECT 'Yuri', 'Tao', 'Yuri Tao', '<row FirstName="Yuri" LastName="Tao" DisplayName="Yuri Tao" />'
Any suggestions? Thanks!
September 29, 2010 at 2:49 pm
Will something like this help:
SELECT *
FROM Students s
CROSS APPLY
(
SELECT
(
SELECT *
FROM Students t
WHERE t.DisplayName = s.DisplayName
FOR XML RAW
) x1
)x
September 30, 2010 at 7:57 am
Works perfectly!! Thank you, thank you!!
September 30, 2010 at 3:29 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply