Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


pull out all values within an XML column


pull out all values within an XML column

Author
Message
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
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
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
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
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 22800
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
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
Genius, Mark! thanks Smile
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
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
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
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 7827
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:


codeId codeValue
1 A
2 B
400 Z


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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • davidandrews13
    davidandrews13
    SSC Eights!
    SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

    Group: General Forum Members
    Points: 824 Visits: 4450
    hey, thats MUCH easier! and more efficient BigGrin

    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
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2272 Visits: 7827
    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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • davidandrews13
    davidandrews13
    SSC Eights!
    SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

    Group: General Forum Members
    Points: 824 Visits: 4450
    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 :-)


    :-D

    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
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2272 Visits: 7827
    Thanks for that, it really wasn't necessary, but much appreciated.

    I will take a look later.

    MM


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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search