[Solved] Insert and Search in XML type Column in SQL Server example

Last Reply on Oct 02, 2015 02:48 AM By Shashikant

Posted on Sep 30, 2015 05:43 AM

hi,

I have xml filed in table sql 

and I have form dynamic for search in this xml filed in asp.net

everything is dynamic I did not know the xml tags

I need send from asp.net to the sp like this 

(Data between '1/1/2014' and '2/2/2015' and ID=50 and ClientID>50)

 

and search in the xml how I can receive this param condition in sql and search in xml file

Results 1 - 5 of 6 12
Posted on Sep 30, 2015 05:43 AM

Please share some Data of your XML field so i can help you out.


Posted on Sep 30, 2015 06:00 AM
rol says:

hi,

I have xml filed in table sql 

and I have form dynamic for search in this xml filed in asp.net

everything is dynamic I did not know the xml tags

I need send from asp.net to the sp like this 

(Data between '1/1/2014' and '2/2/2015' and ID=50 and ClientID>50)

 

and search in the xml how I can receive this param condition in sql and search in xml file

 

<Document>
  <Fields>
    <Date>1/1/2015</Date>
    <ClientID>1</ClientID>
    <Desc>Desc 30 </Desc>
	  <Topics>
		  <topic>
			  <title>topic title1</title>
			  <details>topic details 1</details>
		  </topic>
		  <topic>
			  <title>topic title2</title>
			  <details>topic details 2</details>
		  </topic>
	  </Topics>
	  
  </Fields>
</Document>

 


Posted on Sep 30, 2015 09:40 AM

Here I have created script that will help you out.

SQL

DECLARE @temp AS TABLE(ID INT IDENTITY(1,1),Name VARCHAR(20),Document XML)

INSERT INTO @temp VALUES
('David','<Document><Fields><Date>1/1/2015</Date><ClientID>25</ClientID><Desc>Desc 30 </Desc><Topics><topic><title>topic title1</title><details>topic details 1</details></topic><topic><title>topic title2</title><details>topic details 1</details></topic></Topics></Fields></Document>'),
('Jhon','<Document><Fields><Date>1/2/2015</Date><ClientID>50</ClientID><Desc>Desc 25 </Desc><Topics><topic><title>topic title1</title><details>topic details 1</details></topic><topic><title>topic title2</title><details>topic details 2</details></topic></Topics></Fields></Document>'),
('Kevin','<Document><Fields><Date>1/5/2015</Date><ClientID>55</ClientID><Desc>Desc 55 </Desc><Topics><topic><title>topic title1</title><details>topic details 1</details></topic><topic><title>topic title2</title><details>topic details 3</details></topic></Topics></Fields></Document>')

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
DECLARE @Id INT
DECLARE @MaxClientID INT

SET @FromDate = '1/1/2014'
SET @ToDate =  '2/2/2015'
SET @Id = 3
SET @MaxClientID = 50

SELECT * FROM @temp
WHERE CAST([Document].value('(/Document//Date/node())[1]', 'nvarchar(max)') AS DATETIME) BETWEEN @FromDate AND @ToDate
AND ID = @Id
AND CAST([Document].value('(/Document//ClientID/node())[1]', 'nvarchar(max)') AS INT) > 50

Screenshot


Posted on Sep 30, 2015 02:31 PM

sorry but I must make search dynamic I have not know tags of xml here u write specific tag as 

document//Date/Node

I need send from asp.net to the sp like this 

(Data between '1/1/2014' and '2/2/2015' and ID=50 and ClientID>50)

and need sql paars this and search 


Posted on Oct 01, 2015 04:39 PM
Shashikant says:

Here I have created script that will help you out.

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE @temp AS TABLE(ID INT IDENTITY(1,1),Name VARCHAR(20),Document XML)
 
INSERT INTO @temp VALUES
('David','<Document><Fields><Date>1/1/2015</Date><ClientID>25</ClientID><Desc>Desc 30 </Desc><Topics><topic><title>topic title1</title><details>topic details 1</details></topic><topic><title>topic title2</title><details>topic details 1</details></topic></Topics></Fields></Document>'),
('Jhon','<Document><Fields><Date>1/2/2015</Date><ClientID>50</ClientID><Desc>Desc 25 </Desc><Topics><topic><title>topic title1</title><details>topic details 1</details></topic><topic><title>topic title2</title><details>topic details 2</details></topic></Topics></Fields></Document>'),
('Kevin','<Document><Fields><Date>1/5/2015</Date><ClientID>55</ClientID><Desc>Desc 55 </Desc><Topics><topic><title>topic title1</title><details>topic details 1</details></topic><topic><title>topic title2</title><details>topic details 3</details></topic></Topics></Fields></Document>')
 
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
DECLARE @Id INT
DECLARE @MaxClientID INT
 
SET @FromDate = '1/1/2014'
SET @ToDate =  '2/2/2015'
SET @Id = 3
SET @MaxClientID = 50
 
SELECT * FROM @temp
WHERE CAST([Document].value('(/Document//Date/node())[1]', 'nvarchar(max)') AS DATETIME) BETWEEN @FromDate AND @ToDate
AND ID = @Id
AND CAST([Document].value('(/Document//ClientID/node())[1]', 'nvarchar(max)') AS INT) > 50

Screenshot

 thank u very much but I need send param from asp.net as this 

(Data between '1/1/2014' and '2/2/2015' and ID=50 and ClientID>50)

and parse this in sql to I can search in the XML