January 21, 2015 at 4:27 pm
Hello All,
I have an application that stores xml data in an unusal manor. Basically a SQL Key column and an XML string.
The XML string is not really standard XML, but it is what it is, and I'm stuck with it.
It is in the format;
<row key="Value.01" xml:space="preserve"><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>
I am able to pull values out via
SELECT
p.value('(./c1)[1]', 'VARCHAR(8000)') AS c1,
p.value('(./c2)[1]', 'VARCHAR(8000)') AS c2
FROM dbo.UserXMLTable
CROSS APPLY XMLRECORD.nodes('/row') t(p)
where p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
However I've been struggling with selecting row with a LIKE clause. Something like ;
SELECT *
FROM dbo.F_UserXMLTable
where XMLRECORD.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
I have tried a number of permutations of XML syntax but so far have been stumpled.
Please note "<row key="Value.01" xml:space="preserve">" has a <SP> in the name 'row key' .
Any thoughts or help would be much appreciated.
January 21, 2015 at 5:22 pm
What exactly are you trying to get out of that second SELECT?
Is this what you are looking for?
SELECT XMLRECORD
FROM (SELECT @xml) a (XMLRECORD)
CROSS APPLY XMLRECORD.nodes('/row') t(p)
where p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%';
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2015 at 3:43 am
AronsonAa (1/21/2015)
Please note "<row key="Value.01" xml:space="preserve">" has a <SP> in the name 'row key' .
Oh, no, 'row key' is not a name. Those are two names, name of the element and name of the attribute of the element. And it's quite valid xml structure.
Your first query is suitable for extracting any other columns from the table, if it's what you are trying to do. Play with this query
with UserXmlTable as (
select id = 1, col2='x'
, xmlrecord = cast ('<row key="Value.01" xml:space="preserve"><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>' as xml)
union all
select id = 2, col2='y'
, xmlrecord = cast ('<row key="Value.01" xml:space="preserve"><c1>OtherName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>' as xml)
union all
select id = 3, col2='z'
, xmlrecord = cast ('<row key="Value.01" xml:space="preserve"><c1>OtherName</c1><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>' as xml)
)
SELECT id, col2,
p.value('(./c1)[1]', 'VARCHAR(8000)') AS c1,
p.value('(./c2)[1]', 'VARCHAR(8000)') AS c2
FROM UserXMLTable
CROSS APPLY XMLRECORD.nodes('/row') t(p)
WHERE p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
Note xml in the id=3 row contains <c1>FirstName</c1> but the row isn't returned as WHERE checks the first oocurence of <c1>
January 22, 2015 at 3:27 pm
Many thanks for the help.
I ended up playing with the form of;
SELECT * FROM dbo.F_UserXMLTable t WHERE XMLRECORD.exist(N'/row[some $t in c1/text() satisfies contains($t, "First")]') = 1
I'm not sure this is the best way, but it does seems to satisify my needs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply