Home > Article > Backend Development > Detailed explanation of code examples for RAW mode of FOR XML

Detailed explanation of code examples for RAW mode of FOR XML

黄舟Original: 2017-03-22 17:03:132233browse

Description:

raw mode converts each row in the query result set into an xml element with the 2bef0154f7bc75ad48fa20d0234d078f 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 2bef0154f7bc75ad48fa20d0234d078f element.

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

Syntax:

FOR XML
RAW [ (&#39;ElementName&#39;) ] 
    [ 
       <CommonDirectives> 
       [ , { XMLDATA | XMLSCHEMA [ (&#39;TargetNameSpaceURI&#39;) ]} ] 
       [ , ELEMENTS [ XSINIL | ABSENT ] 
    ] <CommonDirectives> ::= 
   [ , BINARY BASE64 ]
   [ , TYPE ]
   [ , ROOT [ (&#39;RootName&#39;) ] ]

For details, see the example:

Create 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:
##idbody1aaaa2bbbb##34

cccc

Example sentence:

A. Return the query data information, use for xml raw mode

/*
结果:
    <row id="1" body="aaaa" />
    <row id="2" body="bbbb" />
    <row id="3" body="dddd" />
    <row id="4" />
*/select * from base for xml raw;

Make the result set appear in the form of child elements by specifying the ELEMENTS directive.

/*
结果:
    <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;

We noticed that the body with ID 4 is not displayed in this example sentence.

The 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;

For each piece of data, it is uncomfortable to display the 2bef0154f7bc75ad48fa20d0234d078f element. How to modify the 2bef0154f7bc75ad48fa20d0234d078f element? The name is another name.

C. Rename the 2bef0154f7bc75ad48fa20d0234d078f 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(&#39;baseinfo&#39;),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

You can use root to specify, the default root element of the root directive is 78017bea8d3437b4155fd7d6f4aef931

/*
结果:
    <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(&#39;baseinfo&#39;),root(&#39;base&#39;),elements xsinil;

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 1d029f6197b5a3eb8a3fdf0a088ddf55 element of xml, the How to modify it?

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(&#39;baseinfo&#39;),root(&#39;base&#39;),elements xsinil;

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?

/*
结果:
    1aaaa2bbbb3dddd4
*/
--因为id为int类型,为使id不出现列名,我们使id+0
--因为body为nvarchar类型,为使body不出现列名,我们使body+&#39;&#39;select id+0,body+&#39;&#39; from base for xml raw(&#39;&#39;), elements;
However, 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,&#39;,&#39;,isnull(body,&#39;null&#39;)+&#39;&#39;,&#39;;&#39; from base for xml raw(&#39;&#39;),elements;
So 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)+&#39;,&#39;+isnull(body,&#39;null&#39;)+&#39;;&#39; from base for xml raw(&#39;&#39;),elements;

Let’s modify it again so that the result appears in another way.

/*
结果:
    {1,aaaa}{2,bbbb}{3,dddd}{4,null}
*/select &#39;{&#39;+convert(nvarchar,id)+&#39;,&#39;+isnull(body,&#39;null&#39;)+&#39;}&#39; from base for xml raw(&#39;&#39;),elements;

You can see it now So, we can combine according to our own needs to generate the results we need.

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:
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)
Finally, a common example is used to introduce the application of for xml raw mode.

Create student table student, table structure As follows:

Column namesidnameInsert table data as follows:

Data type Null allowed
int allowed
nvarchar(50) allowed

id123

建课程表sclass,表结构如下:

name
张三
李四
王五

列名数据类型允许空

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 &#39;,&#39;+[name]                    
from sclass                    
where cid in (                                    
select cid                                    
from student_class                                    
where student.sid=student_class.sid                                
)                    
for xml raw(&#39;&#39;),elements                
),            
1,1,&#39;&#39;) sclassfrom student

The 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：

The 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

Previous article：Detailed explanation of encoding in xmlNext article：Detailed explanation of encoding in xml

See more

Column name	Data type	Null allowed
id	int	allow
body	nvarchar(50)	Allow

列名	数据类型	允许空
cid	int	允许
name	nvarchar(50)	允许

姓名	课程
张三	语文
张三	数学
张三	英语
李四	语文
王五	数学
王五	英语

Detailed explanation of code examples for RAW mode of FOR XML

Related articles