Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure returning duplicates

Author  Topic 

bbt2d
Starting Member

13 Posts

Posted - 2013-03-01 : 10:58:10
I have a table in Sql Server 2012


CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED
(
[XmlId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


GO


Here is a sample xml snippet


<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Base RevisionNumber="0" Baseid="34433" />
<dev:Rev Time="2013-01-21T15:08:00">
<dev:Person Name="Me" Systemid="54654" />
</dev:Rev>
<dev:Functions Id="A1">
<dev:A1 Number="1">
<dev:Codes>D</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Visitors>
<dev:Visitor Name="Dev01" Location="STLRF">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
<dev:Visitor Name="Dev02" Location="STLRF">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
<dev:Visitor Name="Dev03" Location="FGRTY">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
<dev:Senders>
<dev:Sender Name="FGY(14A)" />
</dev:Senders>
</dev:A1>
</dev:Functions>
<dev:Functions Id="A2">
<dev:A2 Number="1">
<dev:Codes>C</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Remarks>Support</dev:Remarks>
<dev:Notes>Ready</dev:Notes>
<dev:Visitors>
<dev:Visitor Name="GHFF">
<dev:Divisions>
<dev:Division Number="0" Name="Trial" Usage="None">
<dev:FromLocation>LOPO</dev:FromLocation>
<dev:ToLocation>RDSS</dev:ToLocation>
<dev:Description>Rich Filter</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
<dev:Senders>
<dev:Sender Name="W33R" />
</dev:Senders>
<dev:IsReady>true</dev:IsReady>
<dev:IsCall>false</dev:IsCall>
</dev:A2>
<dev:A2 Number="2">
<dev:Codes>A</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Remarks>Loader Ready</dev:Remarks>
<dev:Notes>Ready</dev:Notes>
<dev:Visitors>
<dev:Visitor Name="UDT">
<dev:Divisions>
<dev:Division Number="0" Name="Trial" Usage="None">
<dev:FromLocation>TYUJ</dev:FromLocation>
<dev:ToLocation>DETF</dev:ToLocation>
<dev:Description>Web Enhance</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
<dev:Senders>
<dev:Sender Name="RJ4" />
</dev:Senders>
<dev:IsReady>true</dev:IsReady>
<dev:IsCall>false</dev:IsCall>
</dev:A2>
</dev:Functions>
</dev:Doc>



Insert statement for Xml file:


INSERT INTO XmlTable(XMLDocument)
SELECT * FROM OPENROWSET(
BULK 'C:\Users\123\Desktop\Practice.xml',
SINGLE_BLOB) AS x;



I created this procedure to search the xml return the values and element values associated with it, but it is returning duplicates.

Here is the procedure:


CREATE PROCEDURE [dbo].[GetXmlSearchString]
@findString NVARCHAR(100)
AS
BEGIN

SET NOCOUNT ON

;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT XmlId,


CAST(x.value('.' , 'nvarchar(MAX)') As nvarchar(MAX)) AS Value,
a.value('(@Settings)[1]', 'NVARCHAR(200)') AS Setting,
b.value('(@Id)[1]','nvarchar(MAX)') As Team,
c.value('(@Name)[1]','nvarchar(MAX)') As Visitor

FROM dbo.XmlTable x
CROSS APPLY x.XmlDocument.nodes('//*[contains(text()[1], sql:variable("@findString"))]') a(x)
CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Functions') As bb(b)
CROSS APPLY b.nodes('*/Visitors/Visitor') As cc(c)
--Where a.x.value('.','nvarchar(MAX)') like '%'+@findstring+'%'

RETURN
END


I commented out the where clause because I was trying to get it to return the value if it was upper or lower case, right now it is case sensitive, but I noticed it was returning duplicates when say I searched for Guided.

Thanks for any help!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 11:31:09
How many rows do you expect to get in the output? Try one of these? If it is neither, can you post what you want to see?
--- 1
DECLARE @findstring VARCHAR(255) = 'Guided'
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT XmlId,


CAST(x.value('.' , 'nvarchar(MAX)') As nvarchar(MAX)) AS Value,
a.value('(@Settings)[1]', 'NVARCHAR(200)') AS Setting,
b.value('(@Id)[1]','nvarchar(MAX)') As Team,
c.value('(@Name)[1]','nvarchar(MAX)') As Visitor

FROM dbo.XmlTable x
CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Functions') As bb(b)
CROSS APPLY b.nodes('*/Visitors/Visitor') As cc(c)
CROSS APPLY b.nodes('.//*[contains(text()[1], sql:variable("@findString"))]') a(x)

-- 2
DECLARE @findstring VARCHAR(255) = 'Guided'
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT XmlId,


CAST(x.value('.' , 'nvarchar(MAX)') As nvarchar(MAX)) AS Value,
a.value('(@Settings)[1]', 'NVARCHAR(200)') AS Setting,
b.value('(@Id)[1]','nvarchar(MAX)') As Team,
c.value('(@Name)[1]','nvarchar(MAX)') As Visitor

FROM dbo.XmlTable x
CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Functions') As bb(b)
CROSS APPLY b.nodes('*/Visitors/Visitor') As cc(c)
CROSS APPLY b.nodes('(.//*[contains(text()[1], sql:variable("@findString"))])[1]') a(x)
Go to Top of Page

bbt2d
Starting Member

13 Posts

Posted - 2013-03-01 : 11:46:06
The Second one is what I was looking for James thanks. Did you see the where clause I commented out? I will return the same, but I believed the like operator would scan and return the value even if i type it in as guided or Guided! Right now it will only work if I use Guided!

And looks like I needed to move the @string to the end of all my applys!!! Thanks again!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 12:05:54
You can convert both to upper (or lower case) for case-insensitive comparison - for example:
DECLARE @findstring VARCHAR(255) = UPPER('Guided')
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT XmlId,


CAST(x.value('.' , 'nvarchar(MAX)') As nvarchar(MAX)) AS Value,
a.value('(@Settings)[1]', 'NVARCHAR(200)') AS Setting,
b.value('(@Id)[1]','nvarchar(MAX)') As Team,
c.value('(@Name)[1]','nvarchar(MAX)') As Visitor

FROM dbo.XmlTable x
CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Functions') As bb(b)
CROSS APPLY b.nodes('*/Visitors/Visitor') As cc(c)
CROSS APPLY b.nodes('(.//*[contains(upper-case(text()[1]), sql:variable("@findString"))])[1]') a(x)


http://msdn.microsoft.com/en-us/library/cc645590(v=sql.105).aspx
Go to Top of Page

bbt2d
Starting Member

13 Posts

Posted - 2013-03-01 : 12:32:00
Yea I was reading that earlier, but then it wouldnt return anything I was wanting it to return it whether i typed it in as upper or lower

I was trying something like



ALTER PROCEDURE [dbo].[GetXmlSearchString4]
@findstring NVARCHAR(255) = Upper('@findstring') <-- pretty sure thats what what keeping it from working
AS
BEGIN

SET NOCOUNT ON



;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT XmlId,


CAST(x.value('.' , 'nvarchar(MAX)') As nvarchar(MAX)) AS Value,
a.value('(@Settings)[1]', 'NVARCHAR(200)') AS Setting,
b.value('(@Id)[1]','nvarchar(MAX)') As Team,
c.value('(@Name)[1]','nvarchar(MAX)') As Visitor

FROM dbo.XmlTable x
CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Functions') As bb(b)
CROSS APPLY b.nodes('*/Visitors/Visitor') As cc(c)
CROSS APPLY b.nodes('(.//*[contains(upper-case(text()[1]), sql:variable("@findString"))])[1]') a(x)
--Where a.x.value('.','nvarchar(MAX)') like '%'+@findstring+'%'

RETURN
END

GO
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 12:38:30
Don't do it that way... Do the following:
ALTER PROCEDURE [dbo].[GetXmlSearchString4]
@findstring NVARCHAR(255) <-- pretty sure thats what what keeping it from working
AS
BEGIN

SET NOCOUNT ON

SET @findstring = UPPER(@findstring);

.....
Go to Top of Page

bbt2d
Starting Member

13 Posts

Posted - 2013-03-01 : 12:53:34
Thanks James that got it!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 13:26:36
You are very welcome - glad to help.
Go to Top of Page

bbt2d
Starting Member

13 Posts

Posted - 2013-03-05 : 17:46:40
Hey James today I tried adding Sender as a value, it would pull null again. I have something like this

CROSS APPLY XmlDocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Functions') As bb(b)
CROSS APPLY b.nodes('*') dd(d)
CROSS APPLY d.nodes('Visitors/Visitor') As cc(c)
CROSS APPLY d.nodes('Senders/Sender') ff(f)
CROSS APPLY d.nodes('(.//*[contains(upper-case(text()[1]), sql:variable("@findString"))])[1]') a(x)

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 23:36:14
That can happen if you don't have a Senders/Sender node. If that is the case, change the CROSS APPLY to OUTER APPLY.
Go to Top of Page
   

- Advertisement -