"FOR XML PATH" is a SQL Server query directive to return the rowset as an XML document using "PATH" mode.
Information on the for xml path directive:
Questions tagged [for-xml-path]
279 questions
490
votes
8 answers
How Stuff and 'For Xml Path' work in SQL Server?
Table is:
Id
Name
1
aaa
1
bbb
1
ccc
1
ddd
1
eee
Required output:
Id
abc
1
aaa,bbb,ccc,ddd,eee
Query:
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
…
Puneet Chawla
- 5,729
- 4
- 16
- 33
64
votes
3 answers
How do I avoid character encoding when using "FOR XML PATH"?
I'm looking to create a comma-separated list of values from a SQL Server 2005 table, just like in JanetOhara's question. I'm using a query similar to the one presented in techdo's answer to the question.
Everything is working, except the list of…
dangowans
- 2,263
- 3
- 25
- 40
27
votes
2 answers
SQL Server : FOR XML PATH - nesting / grouping
I have data that looks like:
OrderID CustomerID ItemID ItemName
10000 1234 111111 Product A
10000 1234 222222 Product B
10000 1234 333333 Product C
20000 5678 111111 Product A
20000 5678 222222 …
jared
- 1,344
- 4
- 20
- 38
15
votes
1 answer
FOR XML PATH(''): Escaping "special" characters
This code basically translates characters based on position in one string to the character at the same position in another string and it runs for all rows in the table.
When I run this (simplified version):
DECLARE @R char(40)
DECLARE @U …
KM.
- 101,727
- 34
- 178
- 212
13
votes
7 answers
Replace “<” and “>” with “<” and “>” in sql server
Hi I am new to for xml
I have a query like this
SELECT ProjectId,
ProjectCode,
ProjectName,
TechId,
-- LocationId,
( SELECT GeoId,PoliticalDivisionId…
Kuntady Nithesh
- 11,371
- 20
- 63
- 86
12
votes
3 answers
How to prevent sqlcmd truncation with for xml path query result
After reading one answer and second answer and the infopage on sqlcmd I still cannot get the following to work.
I am trying to query a result into a xml file using a sqlcmd in batch file.
The batchfile looks like this:
sqlcmd -R -d DBName -i…
J3FFK
- 664
- 3
- 14
- 32
10
votes
4 answers
How to use FOR XML PATH('') in a query without escaping special characters?
I have this query:
SELECT DISTINCT
f.CourseEventKey,
(
SELECT f.Title + '; ' AS [text()]
FROM @Facilities
WHERE CourseEventKey = f.CourseEventKey
…
keeehlan
- 7,874
- 16
- 56
- 104
9
votes
1 answer
How FOR XML PATH('') works when concatenating rows
How does the FOR XML PATH ('') clause do its work when concatenating rows in SQL Server?
I just want an explanation of how the FOR XML PATH ('') clause works...
JanLeeYu
- 981
- 2
- 9
- 24
9
votes
3 answers
SQL Server: how to remove last comma after combining rows using XML Path
I found a way to combine multiple row's into one row which is comma separated but now I would like to remove the last comma.
CREATE TABLE supportContacts
(
id int identity primary key,
type varchar(20),
details varchar(30)
);
INSERT…
MattJ
- 319
- 2
- 7
- 17
9
votes
1 answer
TSQL: FOR XML PATH('') Failing To Group
I'm trying to group column values by a specific column using FOR XML PATH('') in TSQL. This is the result in both cases (note that the without XML code - ie: SELECT * FROM @xml - is the same as the with XML code):
Class | …
Question3CPO
- 1,202
- 4
- 15
- 29
8
votes
1 answer
What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT/STRING_AGG)?
I am having a table like this :
EmployeeId EmployeeName ItemName
4 Ganesh Key Board
4 Ganesh Processor
1 Jignesh Key Board
1 Jignesh Mouse
1 Jignesh Processor
3 …
Deepa Mani
- 281
- 2
- 5
- 14
8
votes
3 answers
How to avoid namespace in child nodes using FOR XML PATH?
I want to create a sitemap xml file (including images) directly from the database without another process (like transformation or another trick).
My query is:
;WITH XMLNAMESPACES(
DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9',
…
Guillermo Cullen
- 101
- 1
- 1
- 4
6
votes
2 answers
SQL Server : nesting elements with FOR XML PATH
I want to nest each of my XML elements.
Take the following example:
DECLARE @TempTable TABLE
(
[Column1] char(10),
[Column2] char(10)
);
INSERT INTO @TempTable([Column1], [Column2]) VALUES
('some value', 'some value'),
('some value', 'some…
shane87
- 3,090
- 12
- 51
- 65
5
votes
1 answer
FOR XML PATH and xsi:nil attributes
Good morning all,
I have a large query utilising FOR XML PATH to output a .xml file.I have the main select which basically just represents the root ie
select *
from tbl
for xml path ('root'),elements xsinil
I then have subsequent nested selects…
JordanMazurke
- 1,103
- 10
- 22
5
votes
2 answers
Character length over 130 does not show in column
I have a lot of questions from a survey im using for a pivot table. To collect all the questions to my pivot dynamically im using stuff and for xml path. However it seems like question text > 130 in length is not showing.
And i can select all the…
SqlKindaGuy
- 3,501
- 2
- 12
- 29