Shulou(Shulou.com)06/03 Report--

This article mainly introduces the example analysis of RAW pattern in FOR XML, which is very detailed and has certain reference value. Friends who are interested must finish it!

Description:

The raw schema converts each row of the query result set into an xml element with an element name, and converts the columns of each row into attributes of row.

You can generate a XML hierarchy by writing nested FOR XML queries

By default, all non-null values are mapped to attributes of the element.

If you need to convert the data in the query result set to child elements of the element, you need to use the elements directive.

Syntax:

FOR XMLRAW [('ElementName')] [[, {XMLDATA | XMLSCHEMA [(' TargetNameSpaceURI')]}] [, ELEMENTS [XSINIL | ABSENT]]:: = [, BINARY BASE64] [, TYPE] [, ROOT [('RootName')]]

For more information, see the example:

Create a table Base with the following table structure:

Column name data types allow empty idint allow bodynvarchar (50) allow

Insert the table data as follows:

Idbody1aaaa2bbbb3cccc4

Example:

a. Returns the information of the query data, using for xml raw mode

/ * result: * / select * from base for xml raw

Make the result set appear as child elements by specifying the ELEMENTS instruction.

/ * result: 1 aaaa 2 bbbb 3 dddd 4 * / select * from base for xml raw,elements

We notice that the body display with an id of 4 does not appear in this example.

The reason is that when using the elements directive, if no subsequent command is specified, abscent is used by default, and no element is created for the null value.

In the following example sentence, the null value can be displayed in xml by using elements xsinil.

b. Specify both the elements instruction and the xsinil instruction to produce elements of null column values

/ * result: 1 aaaa 2 bbbb 3 dddd 4 * / select * from base for xml raw,elements xsinil

It looks uncomfortable to display each piece of data as an element, how to change the element name to another name.

c. Rename element

/ * result: 1 aaaa 2 bbbb 3 dddd 4 * / select * from base for xml raw ('baseinfo'), elements xsinil

We all know that every xml file has a root element, how do we add its root element to this xml text.

d. Specify the root element for the xml generated by for xml

It can be specified using root, and the default root element of the root directive is

/ * results: 1 aaaa 2 bbbb 3 dddd 4 * / select * from base for xml raw ('baseinfo'), root (' base'), elements xsinil

So far, the generated xml results seem to be good, but what if we want to change the body column in the database to the element of xml?

e. Modify element name

/ * result: 1 aaaa 2 bbbb 3 dddd 4 * / select id,body data from base for xml raw ('baseinfo'), root (' base'), elements xsinil

Now that the result basically conforms to the basic format of an xml, we imagine that if you don't specify a column name, a root element name, or an element name for id,body, what kind of demerit will occur?

/ * result: 1aaaa2bbbb3dddd4Aaaaaa2bbbb3dd4Aaaaaaaa2bbbb3dd4Aaaaaaaaa2bbbb3dd4Aaaaaaaaa2bbbbb3dd4Aaaaaaaa2bbbb3dddd4Aaaaaaa2bbbb3dddd4Aaaaaa2bbbb3dddd4copyright-because id is int type, in order to make id do not appear column name, we make id+0-- because body is nvarchar type, in order to make body not appear column name, we make body+''select id+0,body+'' from base for xml raw (''), elements

However, for the above results, we do not seem to be able to distinguish each piece of data, and the null value of id 4 is not displayed, how to modify it? See the next sentence.

/ * results: 1 from base for xml raw id+0,',',isnull (body,'null') +',','; 'from base for xml raw (''), elements

In this way, we seem to see the benefits of not being listed. In fact, the last sentence can be revised a little more.

/ * results: 1 from base for xml raw convert (nvarchar,id) +','+ isnull (body,'null') +'; 'from base for xml raw (''), elements

Let's modify it again so that the results appear in another way.

/ * result: {1 isnull aaaa} {2 isnull bbbb} {3 dd} {4 select'{'+ convert (nvarchar,id) +','+ isnull (body,'null') +'} 'from base for xml raw (''), elements

Now we can see that we can combine according to our own needs to produce the results we need.

In SQLServer2005, the xml data type is already supported, so you can write TYPE instructions to return the results of the FOR XML query as the xml data type, as an example:

Declare @ string nvarchar (1000) declare @ xml xml/* message 257, level 16, status 3, line 8 does not allow implicit conversion from data type xml to nvarchar. Use the CONVERT function to run this query. * /-set @ string= (select id,body from base for xml raw,type) set @ xml= (select id,body from base for xml raw,type)

Finally, a common example is given to introduce the application of for xml raw pattern.

Set up the student table student, which is structured as follows:

Column name data types allow empty sidint allow namenvarchar (50) allow

Insert the table data as follows:

Idname1 Zhang San 2 Li Si 3 Wang Wu

Set up the course schedule sclass, the structure of which is as follows:

Column name data types allow empty cidint allow namenvarchar (50) allow

Insert the table data as follows:

Idname1 Chinese 2 Mathematics 3 English

Create a student_class table with the following structure:

Column name data type allows empty sidint

Cidint

Insert data as follows:

Cidsid111213213233

So far, the result of the data is:

Name course Zhang San Chinese Zhang San Mathematics Zhang San English Li Si language Wen Wang Wu maths Learning Wang Wu English

We need the final result in the following form:

Name course Zhang San Chinese, Mathematics, English, four languages, Wen Wang, five Mathematics, English

How to achieve it?

/ * results: Zhang San Chinese, Mathematics, English Li Si Chinese Wang Wu Mathematics, English * / 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 sclassfrom student above are all the contents of the article "sample Analysis of RAW patterns in FOR XML" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.