Detailed explanation of code examples for RAW mode of FOR XML-XML/RSS Tutorial-php.cn

Home

Backend Development

XML/RSS Tutorial

Detailed explanation of code examples for RAW mode of FOR XML

黄舟

Mar 22, 2017 pm 05:03 PM

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 [ (&#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 element. How to modify the element? The name is another name.

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(&#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

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

From XML to Readable Content: Demystifying RSS FeedsApr 11, 2025 am 12:03 AM

RSSfeedsareXMLdocumentsusedforcontentaggregationanddistribution.Totransformthemintoreadablecontent:1)ParsetheXMLusinglibrarieslikefeedparserinPython.2)HandledifferentRSSversionsandpotentialparsingerrors.3)Transformthedataintouser-friendlyformatsliket

Is There an RSS Alternative Based on JSON?Apr 10, 2025 am 09:31 AM

JSONFeed is a JSON-based RSS alternative that has its advantages simplicity and ease of use. 1) JSONFeed uses JSON format, which is easy to generate and parse. 2) It supports dynamic generation and is suitable for modern web development. 3) Using JSONFeed can improve content management efficiency and user experience.

RSS Document Tools: Building, Validating, and Publishing FeedsApr 09, 2025 am 12:10 AM

How to build, validate and publish RSSfeeds? 1. Build: Use Python scripts to generate RSSfeed, including title, link, description and release date. 2. Verification: Use FeedValidator.org or Python script to check whether RSSfeed complies with RSS2.0 standards. 3. Publish: Upload RSS files to the server, or use Flask to generate and publish RSSfeed dynamically. Through these steps, you can effectively manage and share content.

Securing Your XML/RSS Feeds: A Comprehensive Security ChecklistApr 08, 2025 am 12:06 AM

Methods to ensure the security of XML/RSSfeeds include: 1. Data verification, 2. Encrypted transmission, 3. Access control, 4. Logs and monitoring. These measures protect the integrity and confidentiality of data through network security protocols, data encryption algorithms and access control mechanisms.

XML/RSS Interview Questions & Answers: Level Up Your ExpertiseApr 07, 2025 am 12:19 AM

XML is a markup language used to store and transfer data, and RSS is an XML-based format used to publish frequently updated content. 1) XML describes data structures through tags and attributes, 2) RSS defines specific tag publishing and subscribed content, 3) XML can be created and parsed using Python's xml.etree.ElementTree module, 4) XML nodes can be queried for XPath expressions, 5) Feedparser library can parse RSSfeed, 6) Common errors include tag mismatch and encoding issues, which can be validated by XMLlint, 7) Processing large XML files with SAX parser can optimize performance.

Advanced XML/RSS Tutorial: Ace Your Next Technical InterviewApr 06, 2025 am 12:12 AM

XML is a markup language for data storage and exchange, and RSS is an XML-based format for publishing updated content. 1. XML defines data structures, suitable for data exchange and storage. 2.RSS is used for content subscription and uses special libraries when parsing. 3. When parsing XML, you can use DOM or SAX. When generating XML and RSS, elements and attributes must be set correctly.

From XML/RSS to JSON: Modern Data Transformation StrategiesApr 05, 2025 am 12:08 AM

Use Python to convert from XML/RSS to JSON. 1) parse source data, 2) extract fields, 3) convert to JSON, 4) output JSON. Use the xml.etree.ElementTree and feedparser libraries to parse XML/RSS, and use the json library to generate JSON data.

XML/RSS and REST APIs: Best Practices for Modern Web DevelopmentApr 04, 2025 am 12:08 AM

XML/RSS and RESTAPI work together in modern network development by: 1) XML/RSS is used for content publishing and subscribing, and 2) RESTAPI is used for designing and operating network services. Using these two can achieve efficient content management and dynamic updates.

See all articles