February 2, 2015 at 11:02 pm
I'm using FOR XML PATH in a select statement to return a multi-nested XML output. It's been working fine, but I was recently informed that we need to code for the same tag name possibly on up to 4 fields. These fields all fit together....Code 1, Code 2, Code 3, Code 4 may or may not contain data, but if they do, the xml tags must be named the same in the output as shown here with the data coming from the 4 separate fields in a SQL table. If blank, end tag is optional.
<code>1</code>
<code>2</code>
<code>3</code>
</code>
[highlight=#ffff11]Is it possible to have the same tag name when using FOR XML PATH? If so, how? [/highlight] The research I've found points to 'No', but I might not be using the right keywords when searching because I'm really not finding much. If I try to use the same tag name, the output becomes:
<code>123</code>
and concatenates it all together into one field which blows up when sent to the client to parse in their system since the field is define as each one being varchar (1).
Please share any thoughts, tips, advice and/or knowledge. All is appreciated. Thank you!
February 2, 2015 at 11:39 pm
Quick thought, maybe this will help
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<ROOT><code>1</code><code>2</code><code>3</code><code /></ROOT>';
SELECT
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS ELEMENT_RID
,ROOT.DATA.value('local-name(.)','VARCHAR(50)') AS ELEMENT_NAME
,ROOT.DATA.value('.[1]','VARCHAR(50)') AS ELEMENT_VALUE
FROM @TXML.nodes('ROOT/*') AS ROOT(DATA);
Results
ELEMENT_RID ELEMENT_NAME ELEMENT_VALUE
------------ ------------- --------------
1 code 1
2 code 2
3 code 3
4 code
February 3, 2015 at 12:37 pm
What is the query (without for xml) that you need output in the format you like? Are the codes in your xml columns in the table you are querying?
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply