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.
Author |
Topic |
bbt2d
Starting Member
13 Posts |
Posted - 2013-03-01 : 10:58:10
|
I have a table in Sql Server 2012CREATE 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)ASBEGIN 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 VisitorFROM dbo.XmlTable xCROSS 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+'%' RETURNEND 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?--- 1DECLARE @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 VisitorFROM dbo.XmlTable xCROSS 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)-- 2DECLARE @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 VisitorFROM dbo.XmlTable xCROSS 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) |
|
|
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! |
|
|
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 VisitorFROM dbo.XmlTable xCROSS 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 |
|
|
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 lowerI was trying something likeALTER PROCEDURE [dbo].[GetXmlSearchString4] @findstring NVARCHAR(255) = Upper('@findstring') <-- pretty sure thats what what keeping it from working ASBEGIN 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 VisitorFROM dbo.XmlTable xCROSS 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+'%' RETURNENDGO |
|
|
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 ASBEGIN SET NOCOUNT ON SET @findstring = UPPER(@findstring); ..... |
|
|
bbt2d
Starting Member
13 Posts |
Posted - 2013-03-01 : 12:53:34
|
Thanks James that got it! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-01 : 13:26:36
|
You are very welcome - glad to help. |
|
|
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 thisCROSS 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) |
|
|
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. |
|
|
|
|
|
|
|