当前位置: 澳门新濠3559 > 数据库 > 正文

最简单的XML格式澳门新濠3559,把格式化的字符串

时间:2019-11-07 13:43来源:数据库
在数据库开发过程中,字符串和关系表的转化是一项基本技能。当字符串中存在分隔符时,有时将其转换成关系表数据,和其他数据表进行join查询,出现这种情况,是因为没有遵守关系

在数据库开发过程中,字符串和关系表的转化是一项基本技能。当字符串中存在分隔符时,有时将其转换成关系表数据,和其他数据表进行join查询,出现这种情况,是因为没有遵守关系数据库的设计范式,没有把字符串拆分成原子项存储,也有可能是数据传参数;有时会遇到相反的情况,需要将关系表的相关数据拼接成一个字符串显示,或传参。

在传递数据时,XML和JSON是最常用的数据格式,SQL Server从很早的版本就开始支持XML格式,而对于JSON格式,SQL Server从2016版本开始支持。大多数数据库系统并没有升级到SQL Server 2016版本,因此在传递格式化的数据时,通常还是使用XML格式。对我而言,查询和解析XML格式的数据需要掌握的知识点较多,MSDN上关于XML的文档,又试图把XML的各个方面都讲解地清清楚楚,以至于内容冗杂,使学习过程变得困难。我十分不喜欢学习这些不常用的数据结构,再说,在平时的数据库开发中,用到XML的地方也很少,可是,一旦在应用程序中用到XML,就只有头疼的份了,既然避不开XML,那就用最简单的方法学习它,了解它,使用它,以备不时之需。写这篇文章,就是以最简单的方式,分享XML最常用的使用方法。

把格式化的字符串转化成关系格式,基本思路分为两种:

一,XML数据格式的简单介绍

  • 利用TSQL的循环语句:每一次循环都插入到关系表变量或临时表中,这种思路是面向过程的编程;
  • 使用XML查询:先把字符串转化成XML格式,再利用XML的nodes()函数,把XML数据转化成关系数据;这种思路是面向集合的编程,建议采用XML查询实现;

1,最简单的XML格式

把关系格式转化成字符串,基本思路分为两种:

XML数据最简单的格式是:

  • 利用TSQL的游标,对字符串执行累加连接,这种思路是面向过程的编程;
  • 利用XML查询的for xml path子句,把关系格式转化成字符串;这种思路是面向集合的编程,建议采用XML查询实现;
  • 开始标签:<tag>
  • 标签的属性,属性值用双引号:<tag id="1" name="azure">,在单个节点中,属性名不能重复,属性之间使用空格分隔,在开始标签中,才能设置属性;
  • 结束标签:</tag>,结束标签不能有属性;
  • 子节点:在开始标签和结束标签,可以包含节点,叫做子节点;
  • 节点值:在开始标签和结束标签的标量值,叫做节点值;

一,将字符串转换成表

2,使用字符串对最简单的XML格式澳门新濠3559,把格式化的字符串转化成关系格式。XML数据赋值

先把字符串转换成XML格式,再利用XML的nodes()函数,把XML数据转化成关系数据,这种实现方式性能快,代码简洁,

数据类型XML用于存储XML格式化的文本数据,在本例中,声明一个XML类型的变量 @xml,并赋值,后文示例都使用该变量用于数据查询。

declare @separator varchar(10)
declare @str varchar(max)

set @separator=','
set @str='54,57,55,56,59'
declare @xml xml
set @xml='
<Expression ID="1" TaxonomyID="1">
  <SubExpression ID="1" OperandType="Tag" Operator="and">
    <Oprand ID="268819" Name="abuse" />
    <Oprand ID="277029" Name="mongohq" />
    <Oprand ID="516813" Name="access" />
  </SubExpression>
  <SubExpression ID="2" OperandType="Tag" Operator="and">
    <Oprand ID="283839" Name="reviews" />
    <Oprand ID="697348" Name="retention" />
  </SubExpression>
  <SubExpression ID="3" OperandType="Tag" Operator="not">
    <Oprand ID="281556" Name="richfaces" />
    <Oprand ID="2993766" Name="rgp" />
  </SubExpression>
</Expression>'

1,把字符串转化成节点值

二,XPath路径表达式

每个子串都是节点值,只需要取出节点值,就可以把节点值转化成关系格式的列值

XPath 使用路径表达式在 XML 文档中选取节点,节点是通过沿着路径选取的,XPath是查询XML数据时必备的参数。

declare @xml xml
set @xml=convert(xml,'<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')

SELECT ids=N.v.value('.', 'int') 
FROM @xml.nodes('/v') N(v)

常用的路径表达式是:

2,把字符串转化成节点属性

  • .   :选取当前节点;
  • ..  :选取当前节点的父节点;
  • /   :从根节点开始;
  • //  :从匹配选择的节点开始选取,而不考虑其位置;
  • *  :通配符,匹配任意字符,或任意节点;
  • node()  :匹配任意节点,跟通配符 * 功能相似; 
  • @PropertyName :选取属性;

每个子串都是节点的属性值,只需要取出节点的属性值,就可以把属性值转化成关系格式的列值

在路径表达式中,跟节点的选取有关的表达式是:

declare @xml xml
set @xml=convert(xml,'<Item v=''' + REPLACE(@str, @separator, '''></Item><Item v=''') + '''></Item>')

SELECT ids=N.v.value('@v', 'int') 
FROM @xml.nodes('/Item') N(v)
  • NodeName:选取指定节点名及其所有子节点;
  • NodeName[N]:选取指定节点集合的第N个节点;
  • NodeName[@Name]:选取当前节点中带有指定属性的节点;

3,内置表值函数(string_split)

三,XML数据的查询(query()函数

SQL Server 2016 新增一个表值函数string_split,用于按照分隔符将字符串分割成表值数据,返回的字段名是Value

@xml.query(’xpath‘)函数,参数是路径表达式,返回XML数据类型的结果,该XML是非类型化(untyped)的。

STRING_SPLIT ( string , separator )  

1,选取节点及其子节点

二,将表数据拼接成字符串

示例中,从根节点Expression开始,选取SubExpression节点及其子节点:

有以下数据表,有两列:ID和txt,ID值有重复,而txt是文本数据;

select @xml.query('/Expression/SubExpression')
create table dbo.test
(
ID int,
txt varchar(10)
)

query()函数返回的结果如下,该查询结果是非类型化的XML数据。

把ID字段相同的txt字段的值拼接成字符串显示

<SubExpression ID="1" OperandType="Tag" Operator="and">
  <Oprand ID="268819" Name="abuse" />
  <Oprand ID="277029" Name="mongohq" />
  <Oprand ID="516813" Name="access" />
</SubExpression>
<SubExpression ID="2" OperandType="Tag" Operator="and">
  <Oprand ID="283839" Name="reviews" />
  <Oprand ID="697348" Name="retention" />
</SubExpression>
<SubExpression ID="3" OperandType="Tag" Operator="not">
  <Oprand ID="281556" Name="richfaces" />
  <Oprand ID="2993766" Name="rgp" />
</SubExpression>
select ID
    ,(select a.txt+'' from dbo.test a where a.ID=t.ID for xml path('')) as descr
from dbo.test t 
group by ID

2,选取指定节点的所有子节点集合

三,奇巧淫技

select @xml.query('/Expression/SubExpression/node()')
select @xml.query('/Expression/SubExpression/*')

在master数据库中,存在一个系统视图:master.dbo.spt_values,该视图包含从0到2047的所有数字,利用这个特性,可以把特定长度的字符串转化成关系格式,实现的代码如下:

结果集是SubExpression节点下的所有子节点: 

;with cte_numbers as 
(
    select number
    from master.dbo.spt_values
    where type='p'
        and number>0
)
select 
    cast(substring(@str, n.number, charindex(@separator ,@str +@separator ,n.number )-n.number)  as  nvarchar(4000)) as item 
    --,n.number
 from cte_numbers n
 where n.number<=len(@str)+1 
    and charindex(@separator,@separator+@str,n.number)=n.number
<Oprand ID="268819" Name="abuse" />
<Oprand ID="277029" Name="mongohq" />
<Oprand ID="516813" Name="access" />
<Oprand ID="283839" Name="reviews" />
<Oprand ID="697348" Name="retention" />
<Oprand ID="281556" Name="richfaces" />
<Oprand ID="2993766" Name="rgp" />

对于该方法,要体会其代码的思路,通过数据序列,从数字1开始,逐个检测分隔符,对字符串进行分割操作,截取子字符串,从而把字符串转化成关系表;由于master.dbo.spt_values只有0到2047个顺序数字,在必要时,可以替换该系统视图,而使用自定义的数据序列表,以增加能够拆分的字符串长度。

四,XML数据的查询(value()函数)

在数据库开发中,实现字符串和关系格式的相互转化,我倾向于使用面向集合的查询,通过面向过程的编程思想来实现,思路直接,比较简单,在此就不再赘述了。

@xml.value('xpath','sql_data_type'),返回XML数据中单个属性的标量值,在使用value()函数时,xpath 参数必须指定返回的是单个值,而value()函数不会去check返回值的数量。

 

一般情况下,即使xml数据只有一个属性值,静态类型化(Static typing)要求,xpath表达式也必须显式指定返回单个标量值,因此,必须指定在xpath函数的末尾添加”[1]“,通常的xpath表达式是”(xpath)[1]“。

参考文档:

select @xml.value('(/Expression/SubExpression[1]/@ID)[1]','int')
select @xml.value('(/Expression/SubExpression/@ID)[1]','int')

Why (and how) to split column using master..spt_values?

五,XML数据的查询(nodes()函数) 

@xml.nodes ('xpath') 函数返回节点的集合,用于把XML数据转换为关系数据表,返回的每一个行都是XML数据类型,语法是:

nodes ('xpath') as table(column)  

通过nodes()函数,返回SubExpression节点及其属性,由于单个节点中,属性名不可能重复,因此,在nodes()函数返回的单个节点中,不需要通过xpath路由,直接获取当前节点的属性值,这样,可以在xpath表达式中直接指定属性,不需要显式以“[1]”结尾。

示例代码如下,在value()函数中,直接指定属性值,表示获取当前节点的属性值:

select t.v.query('.') as SubExpression
    ,t.v.value('@ID','int') as SubExpressionID
    ,t.v.value('@OperandType','varchar(16)') as OperandType
    ,t.v.value('@Operator','varchar(16)') as Operator
from @xml.nodes('/Expression/SubExpression') as t(v)

澳门新濠3559 1

通过cross apply 连接操作,把SubExpression节点下的所有数据都转换为关系型数据,并把该数据存储到临时数据表#Expressions中:

澳门新濠3559 2澳门新濠3559 3

;with cte_Expressions as 
(
    select e.v.query('.') as Expression
        ,e.v.value('@ID','int') as ExpressionID
        ,e.v.value('@TaxonomyID','int') as TaxonomyID
    from @xml.nodes('/Expression') as e(v)
)
,cte_SubExpression as 
(
    select e.ExpressionID
        ,e.TaxonomyID
        ,se.SubExpression
        ,se.SubExpressionID
        ,se.OperandType
        ,se.Operator
    from cte_Expressions e
    cross apply
    (
        select t.v.query('.') as SubExpression
            ,t.v.value('@ID','int') as SubExpressionID
            ,t.v.value('@OperandType','varchar(16)') as OperandType
            ,t.v.value('@Operator','varchar(16)') as Operator
        from e.Expression.nodes('/Expression/SubExpression') as t(v)
    ) as se
)
select p.TaxonomyID
    ,p.ExpressionID
    ,p.SubExpressionID
    ,p.OperandType
    ,p.Operator
    ,d.OperandID
    ,d.OperandName
from cte_SubExpression p
cross apply
(
    select t.v.value('@ID','int') as OperandID
        ,t.v.value('@Name','varchar(32)') as OperandName
    from p.SubExpression.nodes('/SubExpression/Oprand') as t(v)
) as d

View Code

澳门新濠3559 4

六,把行集数据转化为XML数据(for xml path)

把行集数据转化为XML数据,需要用到for xml path子句,该子句的特点是:

path('root') 子句用于指定根节点;

select子句的字段别名用于指定属性,别名中必须使用@符号标识出属性名,例如:'@PropertyName';

在select 子句中,如果不在别名中把字段指定为属性,那么该字段的值作为节点值,节点值分为标量类型和XML类型;

  • 对于标量类型,节点值是标量值;
  • 对于XML类型,节点值是子节点的集合;

例如,要把数据转换为如下的关系型数据结构,其SubExpression字段是一个非类型化的XML数据,要完成这样的数据转换,必须使用for xml path子句和cast()类型转换:

澳门新濠3559 5

<SubExpression ID="1" OperandType="Tag" Operator="not">
  <Oprand ID="268819" Name="abuse" />
  <Oprand ID="277029" Name="mongohq" />
  <Oprand ID="516813" Name="access" />
</SubExpression>
<SubExpression ID="2" OperandType="Tag" Operator="not">
  <Oprand ID="283839" Name="reviews" />
  <Oprand ID="697348" Name="retention" />
</SubExpression>
<SubExpression ID="3" OperandType="Tag" Operator="not">
  <Oprand ID="281556" Name="richfaces" />
  <Oprand ID="2993766" Name="rgp" />
</SubExpression>

使用类型转换的目的,是为了把for xml path返回的字符串转换成XML数据类型,这样,就能以XML格式嵌入到上次的for xml path的结构中,作为子节点:

澳门新濠3559 6澳门新濠3559 7

;with cte_Expressions as 
(
    select distinct ExpressionID
        ,TaxonomyID
    from #Expressions with(nolock)
) 
,cte_SubExpressions as
(
    select o.ExpressionID
        ,o.SubExpressionID
        ,o.OperandType
        ,o.Operator
    from #Expressions o with(nolock)
    group by o.ExpressionID
        ,o.SubExpressionID
        ,operandType
        ,o.Operator
)
select e.TaxonomyID as TaxonomyID
    ,e.ExpressionID as ExpressionID
    ,cast(
    (
        select o.SubExpressionID as '@ID'
            ,o.OperandType as '@OperandType'
            ,case o.Operator when '&' then 'and' else 'not' end as '@Operator'
            ,cast((
                select op.OperandID as '@ID'
                    ,op.OperandName as '@Name'
                from #Expressions op with(nolock)
                where o.ExpressionID=op.ExpressionID
                    and o.SubExpressionID=op.SubExpressionID
                for xml path('Oprand')            
            )as xml)
        from cte_SubExpressions o
        where o.ExpressionID=e.ExpressionID
        for xml path('SubExpression')
        ) as xml)
        as SubExpressions
from cte_Expressions e

View Code

还有两个函数modify()和exist(),用于XML数据的修改和检查,由于在我当前接触的项目中,没有用到过,我就不写了。

到此,文章也该结尾了,XML的极简用法已经总结了很多,在以后工作中国,如果用到XML时,翻开这篇文章,能够快速解决XML常见的数据查询和解析问题,这样就足够了。

 

参考文档:

Use PATH Mode with FOR XML.aspx)

XML Data (SQL Server)

编辑:数据库 本文来源:最简单的XML格式澳门新濠3559,把格式化的字符串

关键词: