代码之家 › 专栏 › 技术社区 › Irwin

将XML转换为行

xml sql-server

Irwin · 技术社区 · 16 年前

假设我已经将XML存储在数据库中。它包含一个联系人列表,如下所示:

<Person>
 <Name>Irwin</Name>
 <Address>Home, In a place</Address>
 <Contact type="mobile">7771234</Contact>
 <Contact type="home">6311234</Contact>
 <Contact type="work">6352234</Contact>
 <Contact type="fax">6352238</Contact>
</Person>

它存储在SQL Server数据库的XML列中,存储在具有以下结构的表中:

TABLE [Contacts](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [Info] [xml] NOT NULL,
 [Status] [tinyint] NOT NULL,
 [CreateTime] [datetime] NOT NULL,
) ON [PRIMARY]

我想编写一个查询,将contact元素转换为与contacts表的id字段匹配的新表的行。

我试过了:

SELECT  Cast(Request.query('/Person/Contact/text()')  as varchar(100)) as [Number], ID 
FROM Contacts

但它从列中给定的XML片段中提取所有数据,并将所有数据与该行的ID放在一行中,如下所示:

编号

777123463112346352346352281500

当我想要得到的是:

编号

7771234, 1500

6311234, 1500

6352234, 1500

6352238、1500

你能给我指出正确的方向吗?

3 回复 | 直到 16 年前

Stuart Ainsworth 16 年前

使用交叉应用和XML方法

DECLARE @t TABLE ( ID INT, tag XML )

INSERT  INTO @t
        ( ID ,
          tag 
        )
VALUES  ( 1500 , -- ID - int
          '<Person> 
            <Name>Irwin</Name> 
            <Address>Home, In a place</Address> 
            <Contact type="mobile">7771234</Contact> 
            <Contact type="home">6311234</Contact> 
            <Contact type="work">6352234</Contact> 
            <Contact type="fax">6352238</Contact>
          </Person>'          
        ) 

SELECT  Number = Contact.value('.', 'varchar(MAX)') ,
        t.id
FROM    @t t
        CROSS APPLY tag.nodes('/Person/Contact') AS tag ( Contact )

Murph 16 年前

只有指针…

对于结果中需要的每一列,都需要一个显式的xpath查询。你提出的问题 全部的 从XML到单列的文本。

CheeseConQueso 16 年前

如果数字的长度相同(7),请尝试使用子字符串

select 
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),0,8) mobile,
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),8,16) home,
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),16,24) work,
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),24,32) fax,
id
from contacts

推荐文章