将一个XML文本显示成行

最新推荐文章于 2020-10-30 16:10:05 发布

zoujp_xyz

最新推荐文章于 2020-10-30 16:10:05 发布

阅读量439

点赞数

分类专栏： SQL Server 文章标签： xml date encoding 2010 n2 存储

SQL Server 专栏收录该内容

36 篇文章

订阅专栏

本文介绍使用SQL Server中的XML数据类型及方法进行数据提取的过程。通过具体案例演示如何利用nodes、query和value等函数来解析XML数据，适用于需要从XML文件中提取结构化数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原帖：http://blog.youkuaiyun.com/htl258/archive/2009/04/01/4040080.aspx

DECLARE @doc xml;
SET @doc = ' <biglottos> <N1>1 </N1> <N2>2 </N2> <N3>4 </N3> </biglottos> '

SELECT
    A.x.query( ' N1 ' ).value( ' . ' , ' int ' )N1 ,
    A.x.query( ' N2 ' ).value( ' . ' , ' int ' )N2 ,
    A.x.query( ' N3 ' ).value( ' . ' , ' int ' )N3
FROM @doc .nodes( ' /biglottos ' ) AS A(x)

/*
N1          N2          N3
----------- ----------- -----------
1           2           4

(1 行受影响)

*/

declare @xml xml
set @xml = '
<Person>
<Node>
    <Name>admin</Name>
    <Date>2010-4-6</Date>
</Node>
<Node>
    <Name>lisi</Name>
    <Date>2010-4-6</Date>
</Node>
<Node>
    <Name>wangwu</Name>
    <Date>2010-4-7</Date>
</Node>
</Person> '

SELECT
    A.x.query( ' Name ' ).value( ' . ' , ' varchar(10) ' ) Name ,
    A.x.query( ' Date ' ).value( ' . ' , ' datetime ' ) Date
FROM @xml .nodes( ' //Node ' ) AS A(x)

SELECT

x . value ( 'Name[1]' , 'varchar(10)' ) Name ,

x . value ( 'Date[1]' , 'datetime' ) Date

FROM @xml . nodes ( '//Node' ) AS A ( x )

Name Date

---------- -----------------------

admin 2010-04-06 00:00:00.000

lisi 2010-04-06 00:00:00.000

wangwu 2010-04-07 00:00:00.000

(3 行受影响 )

declare @xml xml
set @xml = '
<Person>
<Node>
    <Name>admin</Name>
    <Date>2010-4-6</Date>
</Node>
<Node>
    <Name>lisi</Name>
    <Date>2010-4-6</Date>
</Node>
<Node>
    <Name>wangwu</Name>
    <Date>2010-4-7</Date>
</Node>
</Person> '

SELECT
    A.x.value( ' (Node/Name)[1] ' , ' varchar(10) ' ) Name1,
    A.x.value( ' (Node/Name)[2] ' , ' varchar(10) ' ) Name2,
    A.x.value( ' (Node/Name)[3] ' , ' varchar(10) ' ) Name3
FROM @xml .nodes( ' Person ' ) AS A(x)

/*
Name1      Name2      Name3
---------- ---------- ----------
admin      lisi       wangwu

(1 行受影响)
*/

-------------------------------------------------------------------

想写一个存储过程，将一个XML文本作为参数输入
XML如：
<?xml version="1.0" encoding="utf-8" ?>
<abcs>
<abc QueryName="LatestExchange">
<QueryColumns>
<Columna>FromCurrency </Columna>
<Columna>ToCurrency </Columna>
<Columna>Date </Columna>
<Columna>Value </Columna>
<Columna>OpenPrice </Columna>
<Columna>HighPrice </Columna>
<Columna>LowPrice </Columna>
<Columna>ClosePrice </Columna>
<Columna>Volume </Columna>
<Columna>OpenInterest </Columna>
<Columna>LastUpdate </Columna>
</QueryColumns>
<QueryParameters>
<Parameter Name="Date"> </Parameter>
</QueryParameters>
</abc>
</abcs>

---------------------------------------
问如何查询，才能得到以下结果

Columna
-----------
ToCurrency
Date
Value
OpenPrice
HighPrice
LowPrice
ClosePrice
Volume
OpenInterest
LastUpdate

-->小梁

DECLARE @doc xml;
SET @doc = ' <?xml version="1.0" encoding="utf-8" ?>
<abcs>
<abc QueryName="LatestExchange">
    <QueryColumns>
      <Columna>FromCurrency </Columna>
      <Columna>ToCurrency </Columna>
      <Columna>Date </Columna>
      <Columna>Value </Columna>
      <Columna>OpenPrice </Columna>
      <Columna>HighPrice </Columna>
      <Columna>LowPrice </Columna>
      <Columna>ClosePrice </Columna>
      <Columna>Volume </Columna>
      <Columna>OpenInterest </Columna>
      <Columna>LastUpdate </Columna>
    </QueryColumns>
    <QueryParameters>
      <Parameter Name="Date" />
    </QueryParameters>
</abc>
</abcs> '

SELECT
    A.x.value( ' . ' , ' varchar(20) ' )
FROM @doc .nodes( ' /abcs/abc/QueryColumns/*[position()>1] ' ) AS A(x);

/*
--------------------
ToCurrency
Date
Value
OpenPrice
HighPrice
LowPrice
ClosePrice
Volume
OpenInterest
LastUpdate

(10 行受影响)
*/