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!
Related labels:source:php.cnPrevious article:Detailed explanation of encoding in xml Next article:XML serialization sample code sharingStatement 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.cnLatest Articles by Author
2023-03-15 16:54:01 2023-03-15 12:26:02 2023-03-14 18:58:01 2023-03-14 11:30:01 1970-01-01 08:00:00 2023-03-16 15:20:01 1970-01-01 08:00:00 1970-01-01 08:00:00 1970-01-01 08:00:00 1970-01-01 08:00:00Latest IssuesRelated TopicsMore>Popular RecommendationsPopular TutorialsMore>
JAVA Beginner's Video Tutorial2484596 Latest DownloadsMore>