SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure returning duplicates		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bbt2d
Starting Member

13 Posts
Posted - 03/01/2013 :  10:58:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 03/01/2013 :  11:31:09  Show Profile  Reply with Quote
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 - 03/01/2013 :  11:46:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 03/01/2013 :  12:05:54  Show Profile  Reply with Quote
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 - 03/01/2013 :  12:32:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 03/01/2013 :  12:38:30  Show Profile  Reply with Quote
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 - 03/01/2013 :  12:53:34  Show Profile  Reply with Quote
Thanks James that got it!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 03/01/2013 :  13:26:36  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

bbt2d
Starting Member

13 Posts
Posted - 03/05/2013 :  17:46:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 03/05/2013 :  23:36:14  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000