Questions tagged [for-xml-path]

"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:

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
1
2 3
18 19