Формат xlm что это
Перейти к содержимому

Формат xlm что это

  • автор:

TYPE directive in FOR XML queries

SQL Server support for the xml (Transact-SQL) data type enables you to optionally request that the result of a FOR XML query be returned as xml by specifying the TYPE directive. This allows you to process the result of a FOR XML query on the server. For example, you can specify an XQuery against it, assign the result to an xml type variable, or write Nested FOR XML queries.

SQL Server returns xml data type instance data to the client as a result of different server-constructs such as FOR XML queries that use the TYPE directive, or where the xml data type is used to return XML instance data values from SQL table columns and output parameters. In client application code, the ADO.NET provider requests this xml data type information to be sent in a binary encoding from the server. However, if you are using FOR XML without the TYPE directive, the XML data comes back as a string type. In any case, the client provider will always be able to handle either form of XML. Note that top-level FOR XML without the TYPE directive cannot be used with cursors.

Examples

The following examples illustrate the use of the TYPE directive with FOR XML queries.

Retrieving FOR XML query results as xml type

The following query retrieves customer contact information from the Contacts table. Because the TYPE directive is specified in FOR XML , the result is returned as xml type.

USE AdventureWorks2022; Go SELECT BusinessEntityID, FirstName, LastName FROM Person.Person ORDER BY BusinessEntityID FOR XML AUTO, TYPE; 

This is the partial result:

Assigning FOR XML query results to an xml type variable

In the following example, a FOR XML result is assigned to an xml type variable, @x . The query retrieves contact information, such as the BusinessEntityID , FirstName , LastName , and additional telephone numbers, from the AdditionalContactInfo column of xml TYPE . Because the FOR XML clause specifies TYPE directive, the XML is returned as xml type and is assigned to a variable.

USE AdventureWorks2022; GO DECLARE @x xml; SET @x = ( SELECT BusinessEntityID, FirstName, LastName, AdditionalContactInfo.query(' declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; //act:telephoneNumber/act:number') as MorePhoneNumbers FROM Person.Person FOR XML AUTO, TYPE); SELECT @x; GO 

Querying results of a FOR XML query

The FOR XML queries return XML. Therefore, you can apply xml type methods, such as query() and value() , to the XML result returned by FOR XML queries.

In the following query, the query() method of the xml data type is used to query the result of the FOR XML query. For more information, see query() Method (xml Data Type).

USE AdventureWorks2022; GO SELECT (SELECT BusinessEntityID, FirstName, LastName, AdditionalContactInfo.query(' DECLARE namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; DECLARE namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; //act:telephoneNumber/act:number ') AS PhoneNumbers FROM Person.Person FOR XML AUTO, TYPE).query('/Person.Person[1]'); 

The inner SELECT . FOR XML query returns an xml type result to which the outer SELECT applies the query() method to the xml type. Note the TYPE directive specified.

This is the result:

  111-111-1111 112-111-1111   

In the following query, the value() method of the xml data type is used to retrieve a value from the XML result returned by the SELECT. FOR XML query. For more information, see value() Method (xml Data Type).

USE AdventureWorks2022; GO DECLARE @FirstPhoneFromAdditionalContactInfo varchar(40); SELECT @FirstPhoneFromAdditionalContactInfo = ( SELECT BusinessEntityID, FirstName, LastName, AdditionalContactInfo.query(' declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; //act:telephoneNumber/act:number ') AS PhoneNumbers FROM Person.Person Contact FOR XML AUTO, TYPE).value(' declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; /Contact[@BusinessEntityID="1"][1]/PhoneNumbers[1]/act:number[1]', 'varchar(40)' ); SELECT @FirstPhoneFromAdditionalContactInfo; 

The XQuery path expression in the value() method retrieves the first telephone number of a customer contact whose BusinessEntityID is 1 .

If the TYPE directive is not specified, the FOR XML query result is returned as type nvarchar(max).

Using FOR XML query results in INSERT, UPDATE, and DELETE (Transact-SQL DML)

The following example demonstrates how FOR XML queries can be used in Data Manipulation Language (DML) statements. In the example, the FOR XML returns an instance of xml type. The INSERT statement inserts this XML into a table.

CREATE TABLE T1(intCol int, XmlCol xml); GO INSERT INTO T1 VALUES(1, ''); GO CREATE TABLE T2(XmlCol xml) GO INSERT INTO T2(XmlCol) SELECT (SELECT XmlCol.query('/Root') FROM T1 FOR XML AUTO,TYPE); GO 

See also

Syntax for STUFF/FOR XML PATH

I boiled down my query for readability and am still getting the error in the various ways I’ve tried to execute it:

SELECT STUFF((SELECT * FROM (SELECT N',' + N'first' UNION SELECT N',' + N'second') FOR XML PATH('')), 1, 1, '') 

Also tried:

SELECT STUFF((SELECT * FROM (SELECT N',' + N'first' UNION SELECT N',' + N'second')) FOR XML PATH(''), 1, 1, '') 

For a while I was getting

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

but amended the code per ExactHelp’s post. UPDATE Based on answer from @NenadZivkovic I came up with the following which did what I was looking to do:

WITH CTE_perca_categories AS ( SELECT category = STUFF((SELECT ', ' + category FROM perca_categories FOR XML PATH('')), 1, 2, '') ) , CTE_perca_tags AS ( SELECT tag = (SELECT ', ' + tag from perca_tags FOR XML PATH('')) ) SELECT CONCAT((SELECT * FROM CTE_perca_categories), (SELECT * FROM CTE_perca_tags)) 

Output (where tag begins with ‘Food’):

Charitable Giving, Nondiscretionary Spending, Saving, Discretionary Spending, Food, Dining, Groceries, Bills 

SQL Server – How to Generate XML Output Using FOR XML? – PART 1

Free Live Event Free Live Training DPD banglore

It was introduced long way back with the arrival of SQL Server 2000.

Well, FOR XML is a row set aggregation function which returns a result set of one row and one column, which has a NVARCHAR (MAX) data type.

FOR XML can be used with these 4 directives:

Will discuss one by one in detail…

FOR XML AUTO

  • It is one of the simplest directives from the rest of others in terms of generating XML output from results of a SELECT query.
  • It returns XML output having nested XML elements.
  • But it doesn’t provide much control over the structure of the XML output.
  • As the name suggests, it automatically identifies the element names and also the hierarchies.
  • The following query will make you understand about it:

This is how the ‘Emp’ table looks like:

Now, if we want to use Select query, then:

SELECT E_Id, E_Name FROM Emp FOR XML AUTO

The results will come as:

2_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

  • The above result resembles more like an XML fragments, rather than an XML document, i.e., a valid xml document should have only one top-level element.
  • A root element can be added to the output of a FOR XML AUTO query, by adding the ROOT directive.
  • ROOT is an optional argument. By default, if we are not passing any text in it, the top element will be “Root”, otherwise, it will be the test which has given in the parameters. See the following example, will explain you further on it-

With the default Root;

SELECT E_Id, E_Name FROM Emp FOR XML AUTO, ROOT

The results come like:

3_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

While passing the parameter on it;

SELECT E_Id, E_Name FROM Emp FOR XML AUTO, ROOT('TCS')

The result can be seen as:

4_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

  • Now, if we take for inner join of two tables then, by default FOR XML AUTO generates elements for each row:
SELECT [Emp].E_Id, [Emp].E_Name, [New_Employees].id_num, [New_Employees].fname FROM Emp INNER JOIN New_Employees ON E_Id = id_num FOR XML AUTO

The result can be seen as:

5_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

6_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

If we want to display, a value as an attribute, then we have to put an “ELEMENTS” directive:

7_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

8_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

Well, this is about FOR XML AUTO from my side, in the next post I would like to continue with the other directives, so please be tuned.

And also comments on this!!

Related Posts

SQL Server Query Cost, Memory Grant & SQLRESERVATIONS Clerk

March 16, 2022 March 30, 2022

SQL Server Memory Troubleshooting

February 20, 2022 March 30, 2022

Performance Monitor Counters

January 10, 2022 March 30, 2022

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

One Comment on “SQL Server – How to Generate XML Output Using FOR XML? – PART 1”

Mike says:

I liked your article,
I will appreciate
1- if you help me to create multi level nested element XML file
2- how can I change name of to for example Thanks a lot
Mike
Email : M_Babapour@yahoo.com

Leave a Reply Cancel reply

Free Live Event


Ad

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *