


Detailed explanation of code examples for RAW mode of FOR XML
Description:
raw mode converts each row in the query result set into an xml element with the
element name, and converts the columns of each row into row attributes.
An XML hierarchy can be generated by writing a nested FOR XML query
By default, all non-null values will be mapped as attributes of the
element.
If you need to convert the data in the query result set into child elements of the
element, you need to use the elements directive.
Syntax:
FOR XML RAW [ ('ElementName') ] [ <CommonDirectives> [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ] [ , ELEMENTS [ XSINIL | ABSENT ] ] <CommonDirectives> ::= [ , BINARY BASE64 ] [ , TYPE ] [ , ROOT [ ('RootName') ] ]
For details, see the example:
A. Return the query data information, use for xml raw modeCreate table Base, the table structure is as follows:
Column name Data type Null allowed id int allow body nvarchar(50) Allow to insert table data as follows:
Example sentence:
##id body 1 aaaa 2 ##3bbbb 4 cccc
Make the result set appear in the form of child elements by specifying the ELEMENTS directive./* 结果: <row id="1" body="aaaa" /> <row id="2" body="bbbb" /> <row id="3" body="dddd" /> <row id="4" /> */select * from base for xml raw;Copy after login
We noticed that the body with ID 4 is not displayed in this example sentence./* 结果: <row> <id>1</id> <body>aaaa</body> </row> <row> <id>2</id> <body>bbbb</body> </row> <row> <id>3</id> <body>dddd</body> </row> <row> <id>4</id> </row> */select * from base for xml raw,elements;Copy after login
For each piece of data, it is uncomfortable to display theThe reason is because when using the elements command, if the following command is not specified, abscent is used by default , no element will be created for the null value at this time.
In the following example sentence, the null value can be displayed in xml by using elements xsinil.
B. Specify the elements directive and xsinil at the same time The instruction is to produce elements with null column values
/* 结果: <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>1</id> <body>aaaa</body> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>2</id> <body>bbbb</body> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>3</id> <body>dddd</body> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>4</id> <body xsi:nil="true" /> </row> */select * from base for xml raw,elements xsinil;Copy after login
We all know that every xml file has a root element, how do we Add its root element to this xml text.C. Rename the
element
/* 结果: <baseinfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>1</id> <body>aaaa</body> </baseinfo> <baseinfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>2</id> <body>bbbb</body> </baseinfo> <baseinfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>3</id> <body>dddd</body> </baseinfo> <baseinfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>4</id> <body xsi:nil="true" /> </baseinfo> */select * from base for xml raw('baseinfo'),elements xsinil;Copy after login
At present, the generated xml result seems to be very good, but if we want to change the body column in the database to the element of xml, the How to modify it?D. Specify the root element for the xml generated by for xml
You can use root to specify, the default root element of the root directive is < root>
/* 结果: <base xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <baseinfo> <id>1</id> <body>aaaa</body> </baseinfo> <baseinfo> <id>2</id> <body>bbbb</body> </baseinfo> <baseinfo> <id>3</id> <body>dddd</body> </baseinfo> <baseinfo> <id>4</id> <body xsi:nil="true" /> </baseinfo> </base> */select * from base for xml raw('baseinfo'),root('base'),elements xsinil;Copy after login
The current result basically conforms to the basic format of an xml. Then, we imagine that if the id is not given , the body specifies the column name, neither the root element name nor the element name, what demerits will occur?E. Modify the element name
/* 结果: <base xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <baseinfo> <id>1</id> <data>aaaa</data> </baseinfo> <baseinfo> <id>2</id> <data>bbbb</data> </baseinfo> <baseinfo> <id>3</id> <data>dddd</data> </baseinfo> <baseinfo> <id>4</id> <data xsi:nil="true" /> </baseinfo> </base> */select id,body data from base for xml raw('baseinfo'),root('base'),elements xsinil;Copy after login
Let’s modify it again so that the result appears in another way./* 结果: 1aaaa2bbbb3dddd4 */ --因为id为int类型,为使id不出现列名,我们使id+0 --因为body为nvarchar类型,为使body不出现列名,我们使body+''select id+0,body+'' from base for xml raw(''), elements;Copy after loginHowever, for the above results, we seem to be unable to distinguish each piece of data clearly, and The null value with id 4 is not displayed. How to modify it? See the next sentence.
/* 结果: 1,aaaa;2,bbbb;3,dddd;4,null; */select id+0,',',isnull(body,'null')+'',';' from base for xml raw(''),elements;Copy after loginSo far, we seem to have seen the benefits of not having a column name. In fact, the previous sentence can be modified some more .
/* 结果: 1,aaaa;2,bbbb;3,dddd;4,null; */select convert(nvarchar,id)+','+isnull(body,'null')+';' from base for xml raw(''),elements;Copy after login
You can see it now So, we can combine according to our own needs to generate the results we need./* 结果: {1,aaaa}{2,bbbb}{3,dddd}{4,null} */select '{'+convert(nvarchar,id)+','+isnull(body,'null')+'}' from base for xml raw(''),elements;Copy after login
Create student table student, table structure As follows:In SQLServer2005, the xml data type has been supported. Therefore, you can write the TYPE instruction to convert the results of the FOR XML query to xml. The data type is returned, for example:
Finally, a common example is used to introduce the application of for xml raw mode.declare @string nvarchar(1000)declare @xml xml/* 消息257,级别16,状态3,第8行 不允许从数据类型xml到nvarchar的隐式转换。请使用CONVERT函数来运行此查询。 */ --set @string=(select id,body from base for xml raw,type)set @xml=(select id,body from base for xml raw,type)Copy after login
Column name
sid Data type Null allowed name int allowed Insert table data as follows: nvarchar(50) allowed id
1 name 2 张三 3 李四 王五 建课程表sclass,表结构如下:
列名 数据类型 允许空 cid int 允许 name nvarchar(50) 允许 插入表数据如下:
id name 1 语文 2 数学 3 英语 建student_class表,表结构如下:
列名 数据类型 允许空 sid int cid int 插入数据如下:
cid sid 1 1 1 2 1 3 2 1 3 2 3 3 至此,数据结果是:
姓名 课程 张三 语文 张三 数学 张三 英语 李四 语文 王五 数学 王五 英语 我们需要最后的结果形式如下:
姓名 课程 张三 语文,数学,英语 李四 语文 王五 数学,英语 该如何实现呢?
/* 结果: 张三 语文,数学,英语 李四 语文 王五 数学,英语 */select [name], stuff( ( select ','+[name] from sclass where cid in ( select cid from student_class where student.sid=student_class.sid ) for xml raw(''),elements ), 1,1,'') sclassfrom studentCopy after loginThe above is the detailed content of Detailed explanation of code examples for RAW mode of FOR XML. For more information, please follow other related articles on the PHP Chinese website!
Statement of this WebsiteThe content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn![]()
Hot AI Tools
![]()
Undresser.AI Undress
AI-powered app for creating realistic nude photos
![]()
AI Clothes Remover
Online AI tool for removing clothes from photos.
![]()
Undress AI Tool
Undress images for free
![]()
Clothoff.io
AI clothes remover
![]()
AI Hentai Generator
Generate AI Hentai for free.
![]()
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌R.E.P.O. Best Graphic Settings4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌Assassin's Creed Shadows: Seashell Riddle Solution2 weeks ago By DDDR.E.P.O. How to Fix Audio if You Can't Hear Anyone4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌R.E.P.O. Chat Commands and How to Use Them4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌![]()
Hot Tools
![]()
Notepad++7.3.1
Easy-to-use and free code editor
![]()
SublimeText3 Chinese version
Chinese version, very easy to use
![]()
Zend Studio 13.0.1
Powerful PHP integrated development environment
![]()
Dreamweaver CS6
Visual web development tools
![]()
SublimeText3 Mac version
God-level code editing software (SublimeText3)
![]()
Hot Topics
CakePHP Tutorial1378
52
See all articles17 ways to solve the kernel_security_check_failure blue screen Feb 12, 2024 pm 08:51 PM
Kernelsecuritycheckfailure (kernel check failure) is a relatively common type of stop code. However, no matter what the reason is, the blue screen error causes many users to be very distressed. Let this site carefully introduce 17 types to users. Solution. 17 solutions to kernel_security_check_failure blue screen Method 1: Remove all external devices When any external device you are using is incompatible with your version of Windows, the Kernelsecuritycheckfailure blue screen error may occur. To do this, you need to unplug all external devices before trying to restart your computer.
Can I open an XML file using PowerPoint? Feb 19, 2024 pm 09:06 PM
Can XML files be opened with PPT? XML, Extensible Markup Language (Extensible Markup Language), is a universal markup language that is widely used in data exchange and data storage. Compared with HTML, XML is more flexible and can define its own tags and data structures, making the storage and exchange of data more convenient and unified. PPT, or PowerPoint, is a software developed by Microsoft for creating presentations. It provides a comprehensive way of
Using Python to merge and deduplicate XML data Aug 07, 2023 am 11:33 AM
Using Python to merge and deduplicate XML data XML (eXtensibleMarkupLanguage) is a markup language used to store and transmit data. When processing XML data, sometimes we need to merge multiple XML files into one, or remove duplicate data. This article will introduce how to use Python to implement XML data merging and deduplication, and give corresponding code examples. 1. XML data merging When we have multiple XML files, we need to merge them
Filtering and sorting XML data using Python Aug 07, 2023 pm 04:17 PM
Implementing filtering and sorting of XML data using Python Introduction: XML is a commonly used data exchange format that stores data in the form of tags and attributes. When processing XML data, we often need to filter and sort the data. Python provides many useful tools and libraries to process XML data. This article will introduce how to use Python to filter and sort XML data. Reading the XML file Before we begin, we need to read the XML file. Python has many XML processing libraries,
Convert XML data to CSV format in Python Aug 11, 2023 pm 07:41 PM
Convert XML data in Python to CSV format XML (ExtensibleMarkupLanguage) is an extensible markup language commonly used for data storage and transmission. CSV (CommaSeparatedValues) is a comma-delimited text file format commonly used for data import and export. When processing data, sometimes it is necessary to convert XML data to CSV format for easy analysis and processing. Python is a powerful
Import XML data into database using PHP Aug 07, 2023 am 09:58 AM
Importing XML data into the database using PHP Introduction: During development, we often need to import external data into the database for further processing and analysis. As a commonly used data exchange format, XML is often used to store and transmit structured data. This article will introduce how to use PHP to import XML data into a database. Step 1: Parse the XML file First, we need to parse the XML file and extract the required data. PHP provides several ways to parse XML, the most commonly used of which is using Simple
Python implements conversion between XML and JSON Aug 07, 2023 pm 07:10 PM
Python implements conversion between XML and JSON Introduction: In the daily development process, we often need to convert data between different formats. XML and JSON are common data exchange formats. In Python, we can use various libraries to convert between XML and JSON. This article will introduce several commonly used methods, with code examples. 1. To convert XML to JSON in Python, we can use the xml.etree.ElementTree module
Handling errors and exceptions in XML using Python Aug 08, 2023 pm 12:25 PM
Handling Errors and Exceptions in XML Using Python XML is a commonly used data format used to store and represent structured data. When we use Python to process XML, sometimes we may encounter some errors and exceptions. In this article, I will introduce how to use Python to handle errors and exceptions in XML, and provide some sample code for reference. Use try-except statement to catch XML parsing errors When we use Python to parse XML, sometimes we may encounter some
![]()