pull out all values within an XML column

Question

Post reply

pull out all values within an XML column

davidandrews13

SSCertifiable

Points: 6792
More actions
August 7, 2012 at 7:47 am

#279924

i'm wondering if something like this is possible in some way:
select
OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'),
--OriginalSchema.value('Form[1]/Codes[1]/code[sql:variable("y.myNum")]','varchar(max)'),
* from (
select id,
ROW_NUMBER() over(partition by id order by id) myNum,
OriginalSchema
from myXMLTable x
cross join TallyTable t
where
t.N <= 400
and x.ID = 1
)y
currently, it doesn't work stating:
The argument 1 of the XML data type method "value" must be a string literal
my goal is to pull out all the 'code' in one set-based manner. There are 400 'code'.
e.g
<Codes>
<code id="1">A</code>
<code id="2">B</code>
<code id="400">Z</code>
</Codes>
is this possible? I know that if i hard code it to be '1' or '2' etc, instead of convert(varchar(10),y.myNum) it will work, but obviously it will only be bringing back the item at that address.
cheers
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 1

EDIT.

i say 'there are 400 code'. what i mean, is that there could be upto 400 'code'.

in practice, 0,1 or more of the 'code' could be filled in.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

Try changing

OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'),

to

OriginalSchema.value('Form[1]/Codes[1]/code[sql:column("y.myNum")][1]','varchar(max)')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 3

Genius, Mark! thanks 🙂

as you can see, i had a play around with the 'sql:variable' syntax, trying to get that to work for me, but your advice works like a dream!

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 4

i've been having a bit more of a play with this and have noticed that the line

OriginalSchema.value('Form[1]/Codes[1]/code[sql:column("y.myNum")][1]','varchar(max)')

is significationly slowing down my query.

each row in the table that houses the XML column is assigned to a Client.

A Client could have any number of rows.

It seems that the more rows there are, the slower it becomes - significantly.

One such Client has 8 rows. each row has an XML column with 400 'code', which i am referencing in the code line above, in this post.

That means it is 'cycling' through 3200 'code' (400 X 8).

with the above line in, it takes 6 seconds. without, it takes less than 1.

i'm wondering if there is a more efficient way of getting the same result?

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

I'm not getting this...

Why are you not just doing something like this:

declare @xml xml = '<Codes>

<code id="1">A</code>

<code id="2">B</code>

<code id="400">Z</code>

</Codes>'

select

nd.value('@id','integer') as codeId

, nd.value('(./text())[1]','char(1)') as codeValue

from @xml.nodes('Codes/code') x(nd)

Producing:

[font="Courier New"]

codeIdcodeValue

1A

2B

400Z[/font]

Perhaps if you post some DDL/DML with test data and tell us what result you want, you will get the answer you need?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 6

hey, thats MUCH easier! and more efficient 😀

thanks

my final code is as follows:

select IndividualCode,RecNum,codeTagPosition,DateReceived from (

select *

, ROW_NUMBER() over (partition by codeTagPosition,RecNum order by DateReceived,codeTagPosition) as myRowNum

from (

select

j.value('@id','integer') as codeTagPosition

, j.value('(./text())[1]','char(1)') as IndividualCode

, OriginalSchema.value('Form[1]/Header[1]/AssessmentNumber[1]','INT') as RecNum

, xm.DateReceived

from

table1 xm

cross apply xm.originalschema.nodes('Form/Codes/code') x(j)

where

xm.act = 1

and xm.state = 'Y'

and schema.value('Form[1]/Header[1]/ClientId[1]','INT') = @ClientId

)t

where

not t.IndividualCode is null

)t2

where t2.myRowNum = 1

i have put that code into a UDF and OUTER APPLYed it in another query.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 7

Excellent, glad you got it working.

I would like to say I understood what you are doing with it and that it looked like a good solution, but I don't, so instead I'll just say congratulations on solving your own problem and thanks for sharing your solution 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 8

mister.magoo (4/9/2013)
Excellent, glad you got it working.
I would like to say I understood what you are doing with it and that it looked like a good solution, but I don't, so instead I'll just say congratulations on solving your own problem and thanks for sharing your solution 🙂

😀

i've attempted to create some test data to show what i was doing!

the main jist, is that i only want to bring back the first instance of the Code being entered, based on the Date Recieved.

so although there are multiple records for the same CLient, who have code id 76 filled, i only bring back 'T' which is the one completed on 2013-03-02 (the first instance)

declare @clientid int = 1234;

with cte_table1(DateReceived,[state],OriginalSchema,active) as (

select '2013-04-01','Y', cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>0</AssessmentNumber></Header><Codes><Code id="1" /><code id="76">O</code></Codes></Form>' as xml),1

union all

select '2013-03-02','Y', cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>1</AssessmentNumber></Header><Codes><Code id="1" /><code id="76">T</code></Codes></Form>'as xml),1

union all

select '2013-01-01','Y',cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>2</AssessmentNumber></Header><Codes><Code id="2" /><code id="7">R</code></Codes></Form>'as xml),1

union all

select '2013-02-27','Y',cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>0</AssessmentNumber></Header><Codes><Code id="3" /><code id="167">S</code></Codes></Form>'as xml),1

union all

select '2012-12-12','Y',cast('<Form><Header><ClientId>3214</ClientId><AssessmentNumber>0</AssessmentNumber></Header><Codes><Code id="300" /><code id="76">Z</code></Codes></Form>'as xml),1

)

select IndividualCode,RecNum,codeTagPosition,DateReceived from (

select *

, ROW_NUMBER() over (partition by codeTagPosition order by DateReceived,codeTagPosition) as myRowNum

from (

select

j.value('@id','integer') as codeTagPosition

, j.value('(./text())[1]','char(1)') as IndividualCode

, OriginalSchema.value('Form[1]/Header[1]/AssessmentNumber[1]','INT') as RecNum

, xm.DateReceived

from

cte_table1 xm

cross apply xm.originalschema.nodes('Form/Codes/code') x(j)

where

xm.active = 1

and xm.state = 'Y'

and OriginalSchema.value('Form[1]/Header[1]/ClientId[1]','INT') = @ClientId

)t

where

not t.IndividualCode is null

)t2

where t2.myRowNum = 1

mister.magoo SSC-Forever Points: 47068 More actions · Answer 9

Thanks for that, it really wasn't necessary, but much appreciated.

I will take a look later.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);