Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pull out all values within an XML column Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 7:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 575, Visits: 3,362
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
Post #1341271
Posted Tuesday, August 7, 2012 8:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 575, Visits: 3,362
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.
Post #1341305
Posted Tuesday, August 7, 2012 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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)')




____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1341318
Posted Tuesday, August 7, 2012 8:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 575, Visits: 3,362
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!
Post #1341320
Posted Monday, April 8, 2013 9:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 575, Visits: 3,362
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?
Post #1439914
Posted Monday, April 8, 2013 9:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 1,786, Visits: 5,682
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1439921
    Posted Tuesday, April 9, 2013 7:23 AM
    Mr or Mrs. 500

    Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

    Group: General Forum Members
    Last Login: Today @ 9:27 AM
    Points: 575, Visits: 3,362
    hey, thats MUCH easier! and more efficient :D

    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.
    Post #1440307
    Posted Tuesday, April 9, 2013 11:23 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 4:19 PM
    Points: 1,786, Visits: 5,682
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1440480
    Posted Wednesday, April 10, 2013 3:30 AM
    Mr or Mrs. 500

    Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

    Group: General Forum Members
    Last Login: Today @ 9:27 AM
    Points: 575, Visits: 3,362
    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

    Post #1440712
    Posted Wednesday, April 10, 2013 3:44 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 4:19 PM
    Points: 1,786, Visits: 5,682
    Thanks for that, it really wasn't necessary, but much appreciated.

    I will take a look later.


    MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1440722
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse