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

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

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>

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

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 

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