October 19, 2010 at 7:17 am
Hey all!
I have a SQL XML GURU question.
I have an XML that I essentially need to walk to get the node name and the node value at a certain point in xml. That is simple enough, however I do not know the name of the nodes, nor how many there will be.
The loop is fine, because I can get the node count just fine by using:
set @ColumnCount = @MessageBody.value('count(//Message/_x0023_ins/*)', 'int')
Next, for each position I want to get the node name and the node value for that position. So, if I hard code the ordinal position, it works just fine:
SET @FieldName = @MessageBody.value('local-name(//Message/_x0023_ins/*[1])', 'varchar(128)')
and
SET @FieldName = @MessageBody.value('data(//Message/_x0023_ins/*[1])', 'varchar(128)')
Those work great. However, I want to change the [1] to a dynamic position.
I have tried to use the following, but I can't get any traction:
SET @FieldName = @MessageBody.value('local-name(//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")])', 'varchar(128)')
and
SET @FieldName = @MessageBody.value('data(//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")])', 'varchar(128)')
When I do this, I get the error:
XQuery [value()]: 'local-name()' requires a singleton (or empty sequence), found operand of type 'element(*,xdt:untyped) *'
Any help would be greatly appreciated!
October 19, 2010 at 8:44 am
Quick update since i have beating my head against the wall on this....
For the data values, I have them working using the following statement:
SET @insValue = @MessageBody.query('data(//Message/_x0023_ins/*)[position()=sql:variable("@LoopCount")]').value('.', 'varchar(1000)')
I cannot get the local-value to work using the same syntax...
SET @FieldName = @MessageBody.query('local-name((//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")]))').value('.', 'varchar(1000)')
I get the error:
XQuery [query()]: 'local-name()' requires a singleton (or empty sequence), found operand of type 'element(*,xdt:untyped) *'
I think I am really really close!
Thanks for any help
October 19, 2010 at 8:48 am
Try these
SET @FieldName = @MessageBody.value('local-name((//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")])[1])', 'varchar(128)')
SET @FieldName = @MessageBody.value('data((//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")])[1])', 'varchar(128)')
____________________________________________________
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/61537October 19, 2010 at 9:02 am
That is perfect! Thanks so much man! I have been spending ages searching for this!
Ryan
October 19, 2016 at 5:10 pm
hi,
hope you can help me with the below issue.
i am trying to assign a dynamic node number in the stored procedure.
below is the xml i have. i need to query special event for each TYPE1 node.
when i append integer manually it works fine. but this will soon be automated and i want this code to get the node numbers on the run based on total no.of nodes present in HOURS_TYPE.
my current(static) code:
DECLARE @XML XML
SET @XML = '<P_RECORDSETRECORD>
<DEVELOPMENT>
<ID>74</ID>
</DEVELOPMENT>
<NATIONALOPS>
</NATIONALOPS>
<MARKETING>
<HOURS>
<SPECIALEVENT>
<TYPE1>ChristmasDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
<SPECIALEVENT>
<TYPE1>BoxingDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
</HOURS>
</MARKETING></P_RECORDSETRECORD>
<P_RECORDSETRECORD>
<DEVELOPMENT>
<ID>91</ID>
</DEVELOPMENT>
<NATIONALOPS>
</NATIONALOPS>
<MARKETING>
<HOURS>
<SPECIALEVENT>
<TYPE1>ChristmasDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
<SPECIALEVENT>
<TYPE1>BoxingDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
<SPECIALEVENT>
<TYPE1>TrainingDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
<SPECIALEVENT>
<TYPE1>DoomsDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
</HOURS>
</MARKETING></P_RECORDSETRECORD>
<P_RECORDSETRECORD>
<DEVELOPMENT>
<ID>21</ID>
</DEVELOPMENT>
<NATIONALOPS>
</NATIONALOPS>
<MARKETING>
<HOURS>
<SPECIALEVENT>
<TYPE1>ChristmasDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
<SPECIALEVENT>
<TYPE1>BoxingDay</TYPE1>
<CLOSED>N</CLOSED>
</SPECIALEVENT>
</HOURS>
</MARKETING></P_RECORDSETRECORD>'
SELECT
@XML.value('(/P_RECORDSETRECORD/DEVELOPMENT/ID)[1]','nvarchar(25)') AS [ID],
@XML.value('(/P_RECORDSETRECORD/MARKETING/HOURS/SPECIALEVENT/TYPE1)[2]','nvarchar(25)') AS [DESCRIPTION]
,@XML.value('(/P_RECORDSETRECORD/MARKETING/HOURS/SPECIALEVENT/CLOSED)[2]','varchar(1)') AS [STORE_CLOSED]
October 20, 2016 at 3:26 am
Not really clear what you're asking, maybe this?
SELECT ROW_NUMBER() OVER(PARTITION BY x1.r1 ORDER BY x2.r2) AS [ID],
x2.r2.value('./text()[1]','VARCHAR(30)') AS [DESCRIPTION],
x2.r2.value('(../CLOSED/text())[1]','VARCHAR(30)') AS [STORE_CLOSED]
FROM @XML.nodes('/P_RECORDSETRECORD/MARKETING/HOURS') AS x1(r1)
CROSS APPLY x1.r1.nodes('SPECIALEVENT/TYPE1') AS x2(r2);
____________________________________________________
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/61537October 20, 2016 at 6:16 am
Thanks for your prompt reply.
i think i didnt explain you clearly what i am looking for!
in the xml i have ID under DEVELOPMENT element.
i want to assign TYPE1 (ChristmasDay,BoxingDay,TrainingDay,DoomsDay) which is under MARKETING/HOURS/SPECIALEVENT.
so, i need to write a select query to get the result like below.
ID TYPE1 CLOSED
74 ChristmasDay N
74 BoxingDay N
91 ChristmasDay N
91 BoxingDay N
91 TrainingDay N
91 DoomsDay N
i have written a query where if i assign node number as 1 i is returning
ID TYPE1 CLOSED
74 ChristmasDay N
91 ChristmasDay N
when node number [2]
it was returning
ID TYPE1 CLOSED
74 BoxingDay N
91 BoxingDay N
my issue was to pass this node number on the run. below query works if i pass the node number[1] manually.
SELECT
DISTINCT c.value('(DEVELOPMENT/ID)[1]','nvarchar(25)') AS [ID],
c.value('(MARKETING/HOURS/SPECIALEVENT/TYPE1)[1]','nvarchar(25)') AS [DESCRIPTION]
,c.value('(MARKETING/HOURS/SPECIALEVENT/CLOSED)[1]','varchar(1)') AS [STORE_CLOSED]
FROM [Landing].[X].[Xml]
CROSS APPLY
XmlCol.nodes('//P_RECORDSETRECORD') T(c)
i tried to parameterize by below query. but didnt work.
declare @t int
set @t = 1
SELECT
DISTINCT c.value('(DEVELOPMENT/ID)[1]','nvarchar(25)') AS [ID],
c.value('(MARKETING/HOURS/SPECIALEVENT/TYPE1[position()=sql:variable("@t")])[1]','nvarchar(25)')
,c.value('(MARKETING/HOURS/SPECIALEVENT/CLOSED[position()=sql:variable("@t")])[1]','nvarchar(25)')
FROM [Landing].[X].[Xml]
CROSS APPLY
XmlCol.nodes('//P_RECORDSETRECORD') T(c)
any idea?
your help is greatly appreciated.
October 20, 2016 at 6:45 am
Looks like a typo, change SPECIALHOURS to SPECIALEVENT
____________________________________________________
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/61537October 20, 2016 at 7:03 am
sorry. still no luck.
declare @t int
set @t = 1
SELECT
DISTINCT c.value('(DEVELOPMENT/ID)[1]','nvarchar(25)') AS [ID],
c.value('(MARKETING/HOURS/SPECIALEVENT/TYPE1[position()=sql:variable("@t")])[1]','nvarchar(25)')
,c.value('(MARKETING/HOURS/SPECIALEVENT/CLOSED[position()=sql:variable("@t")])[1]','nvarchar(25)')
FROM [Landing].[X].[Xml]
CROSS APPLY
XmlCol.nodes('//P_RECORDSETRECORD') T(c)
October 20, 2016 at 7:42 am
basically, if i UNION each select statement with node numbers [1],[2]....etc i get the desired results.
but as i said, i want to pass the node number as a variable on the run.
the reason is i am not sure. in each file how many SPECIALEVENTS we are going to get.
so, my plan is to first sum the SPECIALEVENT/TYPE1 and pass the sum value to a variable then run the procedure by looping only those many number of times to the 'SUM' value.
i can only achieve this by parameterizing the node number(singleton).
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy