Stack Overflow
Stack Overflow

Questions tagged [for-xml]

167 questions
23
votes
1 answer

SQL FOR XML - Outputting data as Elements or as Attributes

I'm trying to export some data from a database in a particular XML format specified by the customer. The XML I produce will be manipulated (presumably by XSLT) by a 3rd party to produce the final output, but I want to formal my XML as close as I can…
CJM
  • 11,908
  • 20
  • 77
  • 115
14
votes
4 answers

TSQL: One Row Per Element With FOR XML

I have a SQL Server 2005 query that generates a large result set (up to several gigabytes): SELECT * FROM Product FOR XML PATH('Product') Running the query generates a single row containing a document with many product elements: Row 1: …
Jaecen
  • 855
  • 1
  • 9
  • 17
13
votes
5 answers

SQL Server 2005 For XML Explicit - Need help formatting

I have a table with a structure like the following: LocationID AccountNumber long-guid-here 12345 long-guid-here 54321 To pass into another stored procedure, I need the XML to look like this: 12345 …
Chris Benard
  • 3,167
  • 2
  • 29
  • 35
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

CTE and FOR XML to generate nested XML

I have an adjacency list in the DB and want to deliver the data in XML format to the client through a SQL SP. I'm trying to use CTE and FOR XML but I am not getting the XML nodes to nest. FYI, this will represent a site map. The Table…
Ed Sinek
  • 4,829
  • 10
  • 53
  • 81
11
votes
2 answers

Entity framework calling a FOR XML stored procedure truncates at 2033 characters

I have a stored procedure which uses a FOR XML statement at the end of it, and returns me some XML. I am using .NET 4 and the Entity Framework and when I do a function import of this stored procedure and try to call it through the Entity Framework…
bgs264
  • 4,572
  • 6
  • 38
  • 72
10
votes
2 answers

Query to get XML output for hierarchical data using FOR XML PATH in SQL Server

I have a table with columns NodeId, NodeName, ParentNodeId and I want to ouput entire table data in the form of Xml like the following using SQL query. I think FOR XML PATH mode in Sql server can be used to achieve this (I use SQL Server 2008) using…
RKP
  • 5,285
  • 22
  • 70
  • 111
9
votes
7 answers

Using CHAR(13) in a FOR XML SELECT

I'm trying to use CHAR(13) to force a new line, but the problem is, I'm doing this within a FOR XML Select statement: SELECT STUFF((SELECT CHAR(13) + Comment FROM myTable FOR XML PATH ('')) , 1, 1, '') The problem with this, is that I…
Casey Crookston
  • 13,016
  • 24
  • 107
  • 193
9
votes
1 answer

Can't set output of 'WITH XMLNAMESPACES...FOR XML PATH' to a variable?

I have a query like the following: ;WITH XMLNAMESPACES ( DEFAULT 'http://www.somewhere.com') SELECT ( 'SOMETHING' ) FOR XML PATH('RootNode'), TYPE Running this works fine. However, I run into troubles when I try to set the XML output to a variable…
YourMomzThaBomb
  • 1,311
  • 2
  • 15
  • 21
8
votes
1 answer

Special For Xml in SqlServer

we have a simple table and need convert to XML Declare @Person TABLE ( [BusinessEntityID] [int] NOT NULL, [PersonType] [varchar](2) NULL, [Title] [varchar](30) NULL, [FirstName] [varchar](30) NULL, [MiddleName] [varchar](30)…
jozi
  • 2,833
  • 6
  • 28
  • 41
6
votes
2 answers

T SQL For XML PATH Group By as Attribute or Element

I have been working on the T-SQL FOR XML with PATH Mode to create a Hierarchy based on the group by field. Below is my query and output. Pls help me with your valuable suggestions. Thank you. Good Day!!! select e.department_id AS…
sriram G
  • 81
  • 1
  • 7
6
votes
1 answer

How to use FOR XML in Subqueries?

I'd like to return the below xml: hello 1 2 I wrote the below code but doesn't work: SELECT 'hello' AS Node1, …
The Light
  • 26,341
  • 62
  • 176
  • 258
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
3 answers

How can I combine CTEs with a FOR XML clause?

I'm trying to generate some XML with various levels of nesting, and at the risk of over-simplifying, the output XML will be loosely of the format: …
Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
5
votes
3 answers

SQL Server 'FOR XML' getting value into "row" element node

This is another 'for xml' question, but I'm unsure whether this can be done without explicit mode. If it can't, then I'll just have to live with it. The following is my select statement currently: SELECT [stream_id] as '@stream_id', …
dansan
  • 465
  • 5
  • 16
1
2 3
…
11 12