Stack Overflow
Stack Overflow

Questions tagged [for-xml-explicit]

In MS SQL Server, the EXPLICIT mode (of a FOR XML clause) provides the most flexibility in generating the XML you want from a query result.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML.

It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape.

The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values and mixed contents.

However, writing EXPLICIT mode queries can be cumbersome.

26 questions
7
votes
4 answers

Suggestions on Troubleshooting "FOR XML EXPLICIT" Msg 6833 "requires parent tags to be opened first" Error

I have inherited a 1000-line stored procedure which produces XML by using FOR XML EXPLICIT. My problem is that it works most of the time. In some scenarios I get the error: Parent tag ID 2 is not among the open tags. FOR XML EXPLICIT requires…
John Saunders
  • 160,644
  • 26
  • 247
  • 397
4
votes
1 answer

Remove xmlns="" name space declaration when using FOR XML EXPLICIT

I'm generating an XML document using FOR XML EXPLICIT: declare @MyTable table (value xml); insert into @MyTable values ('content1') ,('content2') ,('content3'); select 1 as Tag,…
GriGrim
  • 2,891
  • 1
  • 19
  • 33
4
votes
2 answers

SQL Server EXPLICIT mode with FOR XML

I am using SQL Server 2008 and I'm trying to produce a custom xml using EXPLICIT mode with FOR XML. I have the one to many relation having following query select p.personid, p.firstname, a.P_City from tblPeople p with (nolock) left…
d-man
  • 57,473
  • 85
  • 212
  • 296
3
votes
2 answers

SQL XML Explicit problems with creating xml tree (parent-child)

I'm facing some problems with xml explicit in sql server where it doesn't output xml according to the relationship I specified in sql query. The query is done on pubs database and though xml path is easier to use my trainer needs it done on xml…
Faiaz Halim
  • 33
  • 5
2
votes
1 answer

Create XML in SQL (FOR XML EXPLICIT)

Need a little bit help to create some XML in SQL Server. The resulting XML should look like this: …
Alexander Brühl
  • 23
  • 6
2
votes
3 answers

Storing the text of a stored procedure in an XML data type in SQL Server

I need to store the text of all of the stored procedures in a database into an XML data type. When I use, FOR XML PATH, the text within in the stored procedure contains serialized data characters like and for CRLF and ", etc. I…
Gloria Santin
  • 2,066
  • 3
  • 51
  • 124
2
votes
3 answers

SQL Server FOR XML - Basic query

I have been given an XML document that I want to generate via a SQL script, I've not done something like this and haven't been able to find any examples that can lead me to being able to generate the final XML I need (and I'm not sure which of the…
Code Ninja
  • 727
  • 1
  • 7
  • 9
2
votes
1 answer

For XML Explicit Nesting Data Incorrectly

ANSWERED THIS AFTER ALL - OUTLINED BELOW IN CASE IT WILL HELP SOME OTHER UNFORTUNATE SOUL THAT HAS TO USE XML EXPLICIT :) I have the following code which outputs the XML included below. The issue I'm running into is that there should be one…
Rob Horton
  • 785
  • 3
  • 9
  • 27
2
votes
1 answer

Output of "for XML Explicit" Query changes trailing zeros for money columns depending on connection

I have a Microsoft SQL Server query that builds up a temp table and then outputs XML using the "for XML Explicit" syntax like this: select * from @XMLOutput order by [UaarSale!1!NodeOrder!hide], [UaarSale!1!NestingId!hide], Tag for XML…
Mark Elder
  • 3,987
  • 1
  • 31
  • 47
2
votes
2 answers

how to generate xml with element and attribute using xml explicit

im trying to generate xml in the following format: Abc Xyz 1 Test 1 Test 1 …
Harsha
  • 1,161
  • 4
  • 18
  • 38
1
vote
1 answer

Duplicate values FOR XML EXPLICIT

I'm trying to create an XML output from SQL using FOR XML EXPLICIT. It has to be XML EXPLICIT because I'm using a different database, however, this example is using SQL Server. I've declared a simple table @table for the question. It has 2 columns…
user3552829
  • 103
  • 1
  • 4
  • 13
1
vote
0 answers

Issue with nesting queries in sql query for xml explicit for multiple parent tags

Using XML explicit query because customer needs me to use CDATA tags but having issue writing a nested xml for Research area. SELECT 1 AS Tag, 0 AS Parent, NULL AS [row!1], NULL AS [Department!2!Department!Cdata], NULL AS…
Lauren Buxton
  • 95
  • 8
1
vote
2 answers

Different values returned by SQL Server 2000 and SQL Server 2014 when returning results using for xml explicit

We have converted a database from SQL Server 2000 to SQL Server 2014. We require XML output and use T-SQL's for xml explicit option to return XML. The timestamp value has been preserved during the conversion. i.e. A conventional query returns the…
Neil Allen
  • 13
  • 3
1
vote
1 answer

SQL Server FOR XML explicit error in generating same name nodes

i am trying to generate below XML Structure using for XML Explicit …
Nimble Fungus
  • 513
  • 3
  • 22
1
vote
1 answer

XML explicit group by parent tag is not working

I required output like below. But when I execute my code, I am not getting the product properly grouped under each parent (it should be like under "US" country code we need to have three products and under "FR" we need to have two products).
Mohamed
  • 11
  • 3
1
2