Dynamic Ordinal Position with SQL XML?
-
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!
-
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
-
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/61537
-
That is perfect! Thanks so much man! I have been spending ages searching for this!
Ryan
-
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]
-
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/61537 -
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.
-
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/61537 -
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)
-
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply