MSSQL2005 XML字段分解

最新推荐文章于 2021-01-27 19:28:27 发布

EX_NET

最新推荐文章于 2021-01-27 19:28:27 发布

阅读量1.4k

点赞数

分类专栏：软件开发技术文章标签： null NULL table xml XML Xml

本文链接：https://blog.csdn.net/zjianbo/article/details/2072167

版权

软件开发技术专栏收录该内容

77 篇文章 0 订阅

订阅专栏

CREATE TABLE [dbo].[T_Model_Component](
[ComponentID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ComponentName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[MotorModel] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Helper] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Component] [xml] NOT NULL,
CONSTRAINT [PK_T_ComponentItem] PRIMARY KEY CLUSTERED
(
[ComponentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

select ComponentID,ComponentName,MotorModel,Helper,Component from T_Model_Component

SELECT ComponentID,ComponentName, nref.query('.') ITEM
FROM T_Model_Component
CROSS APPLY Component.nodes('/Data/Item') AS R(nref)
where ComponentID='3'