BI-SQL丨XML

这篇具有很好参考价值的文章主要介绍了BI-SQL丨XML。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

XML

SQL Server中,存在一种特殊类型的数据,就是XML数据类型。

可能看到这里,小伙伴都会产生疑惑,XML不是Web语言么?为什么在SQL Server里面也会有XML数据类型?

这个就要从SQL Server的应用开始说起了,众所周知,SQL作为计算机的通用语言之一,在各个领域都存在广泛的应用,也有着各式各样的开发规范要求,但是并不是所有的场景下,开发者都会遵循相关的规范。

这就导致,不同的开发者之间因为开发习惯不同,导致接口之间的对接,存在很大的问题。

例如:小A开发了总部的数仓,某张表一共16个字段;小B开发了分部的数仓,因为分部的业务特色,导致小B开发的某张表多余16个字段;这种情况下,小A和小B之间接口的对接,就会出现很大的差异性。

为了解决这一现状,SQL Server引入了XML类型的字段,这样不需要针对表结构进行更改,可以将差异化类型的数据,存储在XML类型的字段中。

使用实例

例子1:使用query和value查询XML数据。

创建一张表,包含XML类型的数据。

代码如下:

CREATE TABLE XML_TEST1
(
    XMLID INT PRIMARY KEY,
    XMLVALUE XML NOT NULL
)
GO
;


INSERT INTO XML_TEST1
    (XMLID,XMLVALUE)
VALUES
    (1, N'<title>白茶</title><title>黑茶</title>'),
    (2, N'<title>红茶</title>'),
    (3, N'<price>19</price>'),
    (4, N'<price>18</price>'),
    (5, N'<sku>tea</sku>');

我们来看一下数据结果:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

针对XML的数据类型,SQL Server是有专门的查询关键字的,分别是query查询和value查询。

query查询返回的是原始的数据格式,而value查询可以从XML中提取相应的值。

query查询示例:

SELECT XMLVALUE.query('/title') FROM XML_TEST1

结果如下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

注:
query内的字段,为查询条件,即获取XML中数据标签。

value查询示例:

SELECT XML_TEST1.XMLVALUE.value('(/title)[1]', 'nvarchar(max)') AS A1,

结果如下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

注:
1.同query查询一样,value第一参数里面的字段,也是查询条件;
2.value查询条件里面的[1],代表获取数据的位置;

例子2:使用nodes查询XML数据。

创建一张表,包含XML类型的数据。

代码如下:

CREATE TABLE XML_TEST2
(
    XMLID INT PRIMARY KEY,
    XMLVALUE XML NOT NULL
)
GO
;


INSERT INTO XML_TEST2
    (XMLID,XMLVALUE)
VALUES
    (1, N'<title>白茶</title><title>黑茶</title><title>红茶</title>'),
    (2, N'<title>黄茶</title>'),
    (3, N'<title>蓝茶</title>'),
    (4, N'<title>绿茶</title>');

我们来看一下数据结果:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

nodes通常会返回XML数据类型中,符合条件的多个节点数据组合成的多行一列的结果表。

这句话很抽象,我们点击上面的查询结果的第一行,结果如下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

可以看到,实际上在结果的第一行,是存在多行数据的,而nodes的作用通常是将一行中多行的数据,拆分出来。

说到这里,就需要提到另外两种形式的APPLY了。

CROSS APPLY与OUTER APPLY

语法:

<left_table_expression>  {cross|outer} apply <right_table_expression>

APPLY运算的步骤:
1.A1:把右表表达式(<right_table_expression>)应用到左表(<left_table_expression>)输入的行;
2.A2:添加外部行;
3.使用APPLY先计算左输入,然后为左输入中的每一行计算一次右输入。

CROSS APPLY和OUTER APPLY,都会执行A1,进行左右表匹配,而只有OUTER APPLY会执行A2。

简而言之:
如果左表匹配到右表,有空积时,CROSS APPLY空积会被过滤;
反之,OUTER APPLY返回的结果包含空积。
为什么这里会提到这两个APPLY,因为nodes通常会和这两个APPLY搭配使用。

CROSS APPLY查询示例:

SELECT
    XML_TEST1.XMLID,
    T2.C.value('.', 'NVARCHAR(MAX)') AS XMLVALUE
FROM XML_TEST1
  CROSS APPLY XMLVALUE.nodes('/title') AS T2(C)

结果如下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

OUTER APPLY查询示例:

SELECT
    XML_TEST1.XMLID,
    T2.C.value('.', 'NVARCHAR(MAX)') AS XMLVALUE
FROM XML_TEST1
  OUTER APPLY XMLVALUE.nodes('/title') AS T2(C)

结果如下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

从结果上,相信小伙伴也能看出差异,这里白茶给大家解释一下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

在XML_TEST1中,一共有5行数据,只有2行,有title标签,而title标签的2行数据,可以拆分出来白茶、黑茶、红茶共计3行数据。
CROSS APPLY会过滤掉空积,因为左右表有3行数据不匹配,所以返回匹配上的3行数据。
OUTER APPLY不过滤空积,原表有5行数据,拆分之后数据总数为6行,所以返回6行数据。

例子3:利用XML的语法,针对某个分隔符进行拆分。

创建一张数据表。

CREATE TABLE XML_TEST3
(
    XMLID INT PRIMARY KEY,
    XMLVALUE NVARCHAR(200) NOT NULL
)
GO
;


INSERT INTO XML_TEST3
    (XMLID,XMLVALUE)
VALUES
    (1, N'白茶,黑茶,红茶'),
    (2, N'黄茶,紫茶'),
    (3, N'蓝茶'),
    (4, N'绿茶');

我们来看一下结果:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

我们现在,要针对XMLVALUE字段,对分隔符“,”进行拆分。

SELECT 
ReplaceTable.XMLID, 
T2.C.value('.', 'NVARCHAR(MAX)') AS XMLVALUE
FROM
    (SELECT XMLID, 
          CAST('<TEA>'+REPLACE(XMLVALUE,',','</TEA><TEA>')+'</TEA>' AS XML) AS XMLVALUE
    FROM XML_TEST3) AS ReplaceTable
OUTER APPLY ReplaceTable.XMLVALUE.nodes('/TEA') AS T2(C)

结果如下:

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

解释一下:
1.使用CAST函数,将原本的分隔符替换为一个固定的XML标签,并且将其转化为XML数据格式;
2.使用XML中的nodes函数,将转化后的XML数据类型字段进行行拆分,生成一张多行一列的结果表;
3.OUTER APPLY将转化后事实表,与nodes生成的结果表,进行左右关联;
4.使用XML中的value函数,将转化后的XML数据类型字段中的值提取出来。

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库

这里是白茶,一个PowerBI的初学者。
BI-SQL丨XML,PowerBI丨SQL,sql,xml,数据库文章来源地址https://www.toymoban.com/news/detail-597401.html

到了这里,关于BI-SQL丨XML的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Mybatis从0到1 SQL注入 参数占位符 XML配置 动态SQL

    学习完mybatis入门后,我们继续学习mybatis基础操作。 需求说明: 根据资料中提供的《tlias智能学习辅助系统》页面原型及需求,完成员工管理的需求开发。 通过分析以上的页面原型和需求,我们确定了功能列表: 查询 根据主键ID查询 条件查询 新增 更新 删除 根据主键ID删除

    2024年02月16日
    浏览(44)
  • hibernate5 根据xml获取ddl sql语句

    根据以上查看源码后,发现实现可以这样写 仅改一改参数就可以迁移hibernate6

    2024年04月27日
    浏览(28)
  • Mybatis 动态SQL条件查询(注释和XML方式都有)

    需求 : 根据用户的输入情况进行条件查询 新建了一个 userInfo2Mapper 接口,然后写下如下代码,声明 selectByCondition 这个方法 我们先用XML的方式实现 在resources 中创建 Userinfo2XMLMapper.xml 文件  将 Userinfo2XMLMapper.xml 文件中的 namespace 进行修改,改为 userInfo2Mapper 接口中的第一行 package 的

    2024年01月22日
    浏览(40)
  • SQL Server查询计划(Query Plan)——XML查询计划

    ​​​​​​6.4.3.  XML 查询计划 SQL Server中,除了通过GUI工具和相关命令获取图形及文本查询计划外,我们还可以通过相关命令获取XML格式的查询计划,这里惯称其为XML查询计划。 SQL Server 2005版本引入了XML查询计划的新特性,其充分吸收了图形及文本查询计划的优势所在,通

    2024年02月22日
    浏览(43)
  • mybatis xml多表查询,子查询,连接查询,动态sql

    student_type 表 student 表 Student 类 一个学生只有一个年级 Type 类 一个年级有多个学生,所以用 list 下列代码中: 1 resultMap 里面property对应实体类属性,column对应数据库字段名 2 主键用 id 标签 其他用result 3 关联查询(子查询和连接查询) 连接查询查一次 4 一个年级多个学生,所以

    2024年01月21日
    浏览(57)
  • Idea设置Mapper.xml文件中SQL命令格式化

    目录 一、遇到问题 二、解决方法 一、遇到问题  使用Idea给项目的Mapper.xml文件配置sql语句时,发现在Mapper.xml文件里粘贴SQL命令时,Mapper.xml文件中使用 Alt+Shift+L 没有 格式化 成功  粘贴代码(不能自动缩进) 之前也在pom文件中解决了xml没有自动缩进格式化的问题,但是对S

    2024年02月15日
    浏览(50)
  • Mybatis-plus 配置自定义sql(.xml文件)查询语句的步骤

    这是使用Mybatis-plus 的自动生成实体类代码生成.xml文件, 所以他会在java目录下,不在resources目录下 如果在java目录下的xml文件,需要分别配置application.yml和pom.xml文件 type-aliases-package:java目录下边的第一级包名 mapper-locations: classpath:映射器的地址: 类路径:也就是.xml所在的包名

    2024年02月16日
    浏览(57)
  • MyBatis XML 映射文件中的 SQL 语句可以分为动态语句和静态语句

    目录 静态查询: 动态查询: 静态更新: 动态更新: 静态删除: 动态删除: 动态语句和静态语句在 MyBatis 中的作用如下: 静态查询: 静态查询是指在 SQL 语句中执行固定的查询操作,查询的条件和内容是预先确定的,不会随着用户输入或其他条件的改变而改变。以下是一

    2024年01月18日
    浏览(66)
  • 【JaveWeb教程】(27)Mybatis的XML配置文件与Mybatis动态SQL 详细代码示例讲解

    Mybatis的开发有两种方式: 注解 XML 使用Mybatis的注解方式,主要是来完成一些简单的增删改查功能。如果需要实现复杂的SQL功能,建议使用XML来配置映射语句,也就是将SQL语句写在XML配置文件中。 在Mybatis中使用XML映射文件方式开发,需要符合一定的规范: XML映射文件的名称

    2024年02月01日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包