SQL Server:Get All String Occurences (Tags) from Nvarchar(Max) Variable Containing a JSON String

SQL Server Concatenate JSON For Dynamic Queries

The latest query you've posted isn't dynamic either, so I'm unsure why you're using EXEC. As i mentioned in the comments, therefore, this is as simple as using SET:

DECLARE @PropertyPK uniqueidentifier; --SP parameter

DECLARE @JSON nvarchar(MAX);

SET @JSON = (SELECT *
             FROM Contacts
             WHERE PropertyPK = @PropertyPK
             FOR JSON PATH, INCLUDE_NULL_VALUES);

There's no need for @PropertyPK to be cast as a varchar to a dynamic SQL statement; just use proper parametrised SQL.

This is based on guesswork and the OP's latest (but clearly incomplete) question. If this isn't correct, this should get you on the right path, however, having information drip fed makes the question difficult to answer properly.

DECLARE @PropertyPK uniqueidentifier,
        @SearchTerm varchar(50),
        @Limit int,
        @Offset int,
        @SortBy sysname,  --objects have the data type sysname, aka nvarchar(128)
        @SortOrder nvarchar(4); --Guess datatype as it was missing in your sample

DECLARE @JSON nvarchar(MAX);

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SET @JSON = (SELECT {Columns}' + NCHAR(10) + --Replace {Columns} with an actual list of the required columns (NOT *)
           N'             FROM dbo.Contacts' + NCHAR(10) +
           N'             WHERE Address LIKE @Search' + NCHAR(10) +
           N'               AND PropertyPK = @PropertyPK' + NCHAR(10) + --I ASSUME that WHERE is still needed
           N'             ORDER BY ' + QUOTENAME(@SortBy) + N' ' + CASE @SortOrder WHEN N'ASC' THEN N'ASC' WHEN N'DESC' THEN 'DESC' END + NCHAR(10) + --The CASE stops invalid sort orders
           N'             OFFSET @Offset FEETCH NEXT @Limit ROWS ONLY' + NCHAR(10) +
           N'             FOR JSON PATH, INCLUDE_NULL_VALUES);';

PRINT @SQL; --Your best friend
EXEC sp_executesql @SQL,
                   N'@JSON nvarchar(MAX) OUTPUT, @Search varchar(50), @PropertyPK uniqueidentifier, @Offset int, @Limit int',
                   @JSON = @JSON OUTPUT, 
                   @Search = @SearchTerm,
                   @PropertyPK = @PropertyPK,
                   @Offset = @Offset,
                   @Limit = @Limit;

One of the biggest things you need to note here is I have made the SQL SAFE. Your SQL was wide open to SQL injection, which is a huge security flaw. If you don't know/understand SQL injection, I suggest reading about it now. SQL like you have above is a huge problem, and raw string concatenation is an awful idea waiting to be exploited.

Accessing JSON Array in SQL Server 2016 using JSON_VALUE

To get all from phoneNumbers:

DECLARE @json nvarchar(max)=
    '{
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        }
      ]
    }'

    SELECT [Type], [Number]
    FROM OPENJSON( @json, '$.phoneNumbers' ) 
    WITH ([Type] NVARCHAR(25) '$.type', [Number] NVARCHAR(25) '$.number');

How to get value of an XML tag of varchar(MAX) string type?

You'll have to CAST/CONVERT the value of your XML to xml (so I hope they're all valid XML values) and then you can use XQUERY against that converted value:

SELECT V.Employee_XML.value('(Employees/Employee/GovernmentIdentificationNumber/text())[1]','int') AS GovernmentIdentificationNumber
FROM dbo.employee_xml E
     CROSS APPLY (VALUES(TRY_CONVERT(xml,E.employee_xml_string))) V(Employee_XML);

Ideally, however, you should be storing XML data as an xml; that's what it's there for after all.

SQL Server : SELECT JSON Column in this JSON Structure

You have two options to parse this JSON array:

• Using OPENJSON() with explicit schema once - to get the content of each item
• Using OPENJSON() twice - to get the index and the content of each item

JSON:

DECLARE @json varchar(max) = '
[
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 3, 
        "Status": 0
    },
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 7, 
        "Status": 0
    }
]'

Using OPENJSON() with explicit schema once:

SELECT A, Status
FROM OPENJSON(@json) WITH (
   A int,
   Status int
)

Result:

A   Status
6   1
3   0
6   1
7   0

Using OPENJSON() twice:

SELECT 
   j1.[key] AS Index,
   j2.A, j2.Status
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.[value]) WITH (
   A int,
   Status int
) j2 

Result:

Index   A   Status
0       6   1
1       3   0
2       6   1
3       7   0

Of course, you can always access an array item by index:

SELECT 
   JSON_QUERY(@json, '$[0]') AS Item,
   JSON_VALUE(@json, '$[0].A') AS A, 
   JSON_VALUE(@json, '$[0].Status') AS Status

Result:

Item                   A    Status
{"A": 6, "Status": 1}  6    1

How do I split a delimited string so I can access individual items?

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

You can use this simple logic:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

Using T-SQL, return nth delimited element from a string

Here is my initial solution...
It is based on work by Aaron Bertrand http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

I simply changed the return type to make it a scalar function.

Example:
SELECT dbo.GetSplitString_CTE('1,222,2,67,888,1111',',',4)

CREATE FUNCTION dbo.GetSplitString_CTE
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(4000)
AS
BEGIN

   DECLARE @result varchar(4000)    
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
                          Item VARCHAR(4000)
                         )  

   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);  

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   SELECT @result=Item
   FROM @Items
   WHERE position=@ElementNumber

   RETURN @result;
END
GO

---

Related Topics