如果你数据库是通过sqlserver 2008以上版本创建的请绕过:客户以前用的是sqlserver2005 创建的数据库。后来升级到 sqlserver 2008 . 有个业务用到了json查询 .可搜索了一下发现 sqlserver(sqlserver2016除外) 不支持json查询. 在网上搜了一番找到了一个解决方案:IFOBJECT_ID (N‘dbo.parseJSON‘) ISNOTNULLDROPFUNCTION dbo.parseJSONGOIFEXISTS (SELECT*FROM sys.types WHERE name LIKE‘Hierarchy‘)DROP TYPE dbo.Hierarchy
go...
SELECT GYK.JsonTable from dbo.td_YWZC_Config_GYK_History AS GYK[ {"检测对象":"试块型式","纵向缺陷检测1":"","纵向缺陷检测2":"","横向缺陷检测1":"","横向缺陷检测2":"","母材检测":""}, {"检测对象":"探头型号","纵向缺陷检测1":"","纵向缺陷检测2":"","横向缺陷检测1":"","横向缺陷检测2":"","母材检测":""}, {"检测对象":"探头移动区","纵向缺陷检测1":"","纵向缺陷检测2":"","横向缺陷检测1":"","横向缺陷检测2...
客户以前用的是sqlserver2005 创建的数据库。后来升级到 sqlserver 2008 . 有个业务用到了json查询 .可搜索了一下发现 sqlserver(sqlserver2016除外) 不支持json查询. 在网上搜了一番找到了一个解决方案:IF OBJECT_ID (N‘dbo.parseJSON‘) IS NOT NULLDROP FUNCTION dbo.parseJSONGOIF EXISTS (SELECT * FROM sys.types WHERE name LIKE ‘Hierarchy‘)DROP TYPE dbo.Hierarchy
go
CREATE TYPE dbo.Hierarchy AS TABLE
/*Markup l...
---------------------------------------------------
----- 解析字符串函数 -----
---------------------------------------------------
CREATE FUNCTION [dbo].[fn_split](@p_str VARCHAR(8000), @p_split VARCHAR(10))
RETURNS @tab TABLE(tid VARCHAR(2000))
AS
BEGIN
DECLARE @idx INT
DECLARE @len INT
SELECT @len = LEN(@p_split), @idx = CHARINDEX(@p_split, @p_str, 1)
WHILE(@idx >= 1)
BE...
SqlServer?操作?JSON
Intro
Sql Server 从 2016 开始支持了一些 json 操作,最近的项目里也是好多地方直接用字段直接存成了 json ,需要了解一下怎么在 Sql Server 中操作 JSON.
JSON支持适用于 SqlServer 2016 及以上版本 和 Azure SQL Database。
SqlServer 中内置了一些 JSON 相关的方法:
可以判断一段字符串是否是标准的 json(ISJSON)
可以直接查询数据成 json 格式(FOR JSON PATH) 类似于之前的查询一个 xml (FOR XML PATH...
ALTER TABLE Sales.SalesOrderHeader
2 ADD vCustomerName AS JSON_VALUE(Info,‘$.Customer.Name‘)
3
4 CREATE INDEX idx_soh_json_CustomerName
5 ON Sales.SalesOrderHeader(vCustomerName)
JSON 基本操作{"name": "小明","info": {"address": {"province": "河南省","city": "郑州市","district": "郑东新区"},"hobbies": ["篮球","足球","乒乓球"]}
} 1 -- 查询某一属性值2 SET @name = JSON_VALUE(@jsonInfo, ‘$.name‘)...
select * from 表名 where JSON_VALUE(字段名,‘$."json字段名"‘) like ‘%查询关键字符串%‘
$表示object的根
例 select * from Country where JSON_VALUE(Name,‘$."CountryName"‘) like ‘%国%‘
测试数据
id(INT)json(VARCHAR)path1(VARCHAR)1
[10, 20, [30, 40]]
$[2][*]2
{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}
$.ccc.hhh[*]3
{"aaa":"bbb","ccc":{"ddd":"eee","fff":...
如果你一直使用JSON_MODIFY()函数来修改SQL Server中的JSON文档,那么你可能习惯于修改key/value属性的value部分。但是你知道你也可以修改key部分吗?这样做的诀窍是将value复制到一个新键,然后删除旧键。这里有一个基本的例子来说明我的意思。-- Declare a variable and assign some JSON to it
DECLARE @data NVARCHAR(50)={"Name":"Homer"}-- Print the current JSON
PRINT @data-- Rename the key (by copying the value to a ...
CREATE FUNCTION dbo . parseJSON ( @JSON NVARCHAR ( MAX )) RETURNS @hierarchy TABLE ( element_id INT IDENTITY ( 1 , 1 ) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ sequenceNo [int] NULL, /* t
CREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))
RETURNS @hierarchy TABLE(element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives th...
很久之前用到的,现在整理在这,里面一些代码来源于网上,不过有些bug已被我修改了。
1.查询结果转XMLDECLARE @ParameterSQL NVARCHAR(MAX)=SELECT * FROM table;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N@XML XML OUTPUT
SET @SQL = WITH PrepareTable (XMLString)
SET @SQL = @SQL + AS(
SET @SQL = @SQL + @ParameterSQL+ FOR XM...
实现SQL Server 原生数据从XML生成JSON数据的实例代码
SQL Server 是关系数据库,查询结果通常都是数据集,但是在一些特殊需求下,我们需要XML数据,最近这些年,JSON作为WebAPI常用的交换数据格式,那么数据库如何生成JSON数据呢?今天就写了一个DEMO.
1.创建表及测试数据SET NOCOUNT ON IF OBJECT_ID(STATS) IS NOT NULL DROP TABLE STATS
IF OBJECT_ID(STATIONS) IS NOT NULL DROP TABLE STATIONS
IF OBJECT_ID(OPE...
废话不多说了,直接给大家贴代码了。-- create function
create function [dbo].[fnXmlToJson] (@XmlData xml)
returns nvarchar(max)
as
begin
return
(select stuff(
(select
*
from
(select
',{'+
stuff(
(select
',"'+
coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+ b.c.value('text()[]','NVARCHAR(MAX)') +'"'
from x.a.nodes('*') b(c) for xml path(''),type).value('(./text())[]','NVARCHAR(MAX)...
脚本源码:代码如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE[dbo].[SerializeJSON](
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N@XML XML OUTPUT
SET @SQL = WITH PrepareTable (XMLString)
SET @SQL = @SQL + AS(
SET @SQL = @SQL + @ParameterSQL+ FOR XML ...
有时候需要一次性将SQL Server中的数据导出给其他部门的也许进行关联或分析,这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP。
数据导出为XML
在SQL Server 2005之后提供了一个for xml子句在关系数据库中原生支持XML。通过该命令可以将二维关系结果集转换为XML,通过BCP就可以将数据存为XML了。...
有时候需要一次性将SQL Server中的数据导出给其他部门的也许进行关联或分析,这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP。
数据导出为XML
在SQL Server 2005之后提供了一个for xml子句在关系数据库中原生支持XML。通过该命令可以将二维关系结果集转换为XML,通过BCP就可以将数据存为XML了。...