January 24, 2014 at 8:23 am
Hi all,
I try to write a query generating XML code for the first time and face this error "Parent tag ID 3 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first."
If I don't run the "for xml explicit" condition, the result set is:
tagparentDocument!1!CstmeCdtTrfInitn!2!GrpHdr!3!PmtInf!4!MsgId!5!CreDtTm!6!NbOfTxs!7!CtrlSum!8!InitgPty!9!PmtInfId!10!Nm!11!
1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
21NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
32NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
42NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
53NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
63NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
73NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
83NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
93NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
104NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1110NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
For me tag 3 is declared before any reference to it, but I think I miss something.
I would be grateful if anybody could point me on what I'm missing.
Thanks
January 24, 2014 at 9:54 am
What exactly are you trying to do? You posted some sample data but every column other than the first 2 are all NULL. What you have posted appears to be a hierarchy. Why FOR XML?
If you can post ddl and sample data in a consumable format along with an explanation of what you want for results we can help you out very quickly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 2:15 am
Hi Sean and thanks for your answer. I was off over the week-end but now back to work.
Basically what I want to achieve is something like this
<Document>
<CstmrCdtTrfInitn>
<GrpHdr>
<MsgId>20140127094732</MsgId>
<CreDtTm>2014-01-27T09:47:32</CreDtTm>
<NbOfTxs>1</NbOfTxs>
<CtrlSum>15303.10</CtrlSum>
<InitgPty>
<Nm>Test TRF</Nm>
<InitgPty>
</GrpHdr>
<PmtInf>
<PmtInfId>2014-01-27T09:47:32</PmtInfId>
<Nm>TRF</Nm>
<PmtInf>
<GrpHdr>
<MsgId>20140127094733</MsgId>
<CreDtTm>2014-01-27T09:47:33</CreDtTm>
<NbOfTxs>1</NbOfTxs>
<CtrlSum>15303.20</CtrlSum>
<InitgPty>
<Nm>Test TRF</Nm>
<InitgPty>
</GrpHdr>
<PmtInf>
<PmtInfId>2014-01-27T09:47:33</PmtInfId>
<Nm>TRF</Nm>
<PmtInf>
</CstmrCdtTrfInitn>
</Document>
There are actually many other columns after this sample but this is the idea.
I try to achieve this with the FOR EXPLICIT method because I ran into other problems using the FOR AUTO method.
There was no data past the 2nd column because I first tried to get the hierarchy right before trying to solve sorting issues.
This is the code I ran before getting the error message:
selecttag, parent, [Document!1!], [CstmeCdtTrfInitn!2!], [GrpHdr!3!], [PmtInf!4!], [MsgId!5!], [CreDtTm!6!],
[NbOfTxs!7!], [CtrlSum!8!], [InitgPty!9!], [PmtInfId!10!], [Nm!11!]
from
(
select 1 as Tag,
NULL as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 2 as Tag,
1 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 3 as Tag,
2 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 4 as Tag,
2 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 5 as Tag,
3 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 6 as Tag,
3 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 7 as Tag,
3 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 8 as Tag,
3 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 9 as Tag,
3 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 10 as Tag,
4 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
UNION ALL
select 11 as Tag,
4 as Parent,
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CreDtTm!6!],
null as [NbOfTxs!7!],
null as [CtrlSum!8!],
null as [InitgPty!9!],
null as [PmtInfId!10!],
null as [Nm!11!]
--from#eVirements
) as xmltbl
for xml explicit
The code to create and populate the sample test table eVirements is
if object_id (N'tempdb..#eVirements', N'U') is not null
begin
drop table #eVirements
end
create table #eVirements
(
GrpHdr_MsgIdnvarchar(35),
GrpHdr_CreDtTmnvarchar(19),
GrpHdr_NbOfTxsnvarchar(15),
GrpHdr_CtrlSumnvarchar(18),
GrpHdr_InitgPty_Nmnvarchar(70),
PmtInf_PmtInfIdnvarchar(35)
)
insert into #eVirements
(GrpHdr_MsgId, GrpHdr_CreDtTm, GrpHdr_NbOfTxs, GrpHdr_CtrlSum, GrpHdr_InitgPty_Nm,PmtInf_PmtInfId)
values
(
(select rtrim(convert(char(25),getdate(),112)) + rtrim(replace(convert(char(25),getdate(),108),':',''))),
(select convert(char(19),getdate(),126)),
1,
15303.10,
'Test SEPA',
'pol1'
)
insert into #eVirements
(GrpHdr_MsgId, GrpHdr_CreDtTm, GrpHdr_NbOfTxs, GrpHdr_CtrlSum, GrpHdr_InitgPty_Nm,PmtInf_PmtInfId)
values
(
(select rtrim(convert(char(25),getdate(),112)) + rtrim(replace(convert(char(25),getdate(),108),':',''))),
(select convert(char(19),getdate(),126)),
1,
15303.20,
'Test SEPA',
'pol2'
)
I am not new to SQL but fully new to XML. So any help will be much appreciated.
Thanks
January 27, 2014 at 6:45 am
ok, I think I got it ... let me check ...
January 27, 2014 at 7:38 am
ok, so I got a bit further, simplified the query, added the sort order, displayed data and again the same error appearing
The SQL query is ...
select 1 as Tag,
NULL as Parent,
'0' as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CtrlSum!8!],
null as [PmtInfId!10!]
from#eVirements
UNION ALL
select 2 as Tag,
1 as Parent,
PmtInf_PmtInfId as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CtrlSum!8!],
null as [PmtInfId!10!]
from#eVirements
UNION ALL
select 3 as Tag,
2 as Parent,
PmtInf_PmtInfId as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CtrlSum!8!],
null as [PmtInfId!10!]
from#eVirements
UNION ALL
select 4 as Tag,
2 as Parent,
PmtInf_PmtInfId as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CtrlSum!8!],
null as [PmtInfId!10!]
from#eVirements
UNION ALL
select 5 as Tag,
3 as Parent,
PmtInf_PmtInfId as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
GrpHdr_MsgId as [MsgId!5!Element],
GrpHdr_CtrlSum as [CtrlSum!8!],
null as [PmtInfId!10!]
from#eVirements
UNION ALL
select 8 as Tag,
3 as Parent,
PmtInf_PmtInfId as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
GrpHdr_MsgId as [MsgId!5!],
GrpHdr_CtrlSum as [CtrlSum!8!],
null as [PmtInfId!10!]
from#eVirements
UNION ALL
select 10 as Tag,
4 as Parent,
PmtInf_PmtInfId as [Document!1!Sort!Hide],
null as [Document!1!],
null as [CstmeCdtTrfInitn!2!],
null as [GrpHdr!3!],
null as [PmtInf!4!],
null as [MsgId!5!],
null as [CtrlSum!8!],
PmtInf_PmtInfId as [PmtInfId!10!]
from#eVirements
order by 3, 1, 2
for xml explicit
The result of the query without the for xml explicit clause is ...
TagParentDocument!1!Sort!HideDocument!1!CstmeCdtTrfInitn!2!GrpHdr!3!PmtInf!4!MsgId!5!CtrlSum!8!PmtInfId!10!
1NULL0NULLNULLNULLNULLNULLNULLNULL
1NULL0NULLNULLNULLNULLNULLNULLNULL
21pol1NULLNULLNULLNULLNULLNULLNULL
32pol1NULLNULLNULLNULLNULLNULLNULL
42pol1NULLNULLNULLNULLNULLNULLNULL
53pol1NULLNULLNULLNULL2014012708242715303.10NULL
83pol1NULLNULLNULLNULL2014012708242715303.10NULL
104pol1NULLNULLNULLNULLNULLNULLpol1
21pol2NULLNULLNULLNULLNULLNULLNULL
32pol2NULLNULLNULLNULLNULLNULLNULL
42pol2NULLNULLNULLNULLNULLNULLNULL
53pol2NULLNULLNULLNULL2014012708242715303.20NULL
83pol2NULLNULLNULLNULL2014012708242715303.20NULL
104pol2NULLNULLNULLNULLNULLNULLpol2
And this is what I want to achieve is ...
<Document>
<CstmeCdtTrfInitn>
<GrpHdr>
<MsgId>20140127082427</MsgId>
<CtrlSum>15303.10</CtrlSum>
</GrpHdr>
<PmtInf>
<PmtInfId>pol1</PmtInfId>
</PmtInf>
</CstmeCdtTrfInitn>
<CstmeCdtTrfInitn>
<GrpHdr>
<MsgId>20140127082427</MsgId>
<CtrlSum>15303.20</CtrlSum>
</GrpHdr>
<PmtInf>
<PmtInfId>pol1</PmtInfId>
</PmtInf>
</CstmeCdtTrfInitn>
</Document>
Everything is fine as long as the <GrpHdr> tag is alone. The error message pops up as soon as I incorporate the <PmtInf> tag which is on the same level as <GrpHdr>.
Can anyone please tell me what I'm doing wrong here?
Thanks a lot ...
January 27, 2014 at 8:28 am
Searched and found!
January 27, 2014 at 9:26 am
rot-717018 (1/27/2014)
Searched and found!
Glad you were able to figure it out. It is customary around here to post your solution so that other stumbling across this thread in the future can see how you solved it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2014 at 12:48 am
Sorry for the delay ... I wanted to post my solution but I was stuck with deadlines!
So, as a newbie, in XML using a SQL query ,my approach was to build all the hierarchie up first (thus remming out "--from #eVirements" in my query) and then try to fill the table with relevant data. That was wrong and caused part of the error. The second error was in the order I declared the tags.
To summarize, this is what you should do:
1. declare the tags in the order they would appear in your select query
2. use the table that holds the data (from clause) from the beginning on
An example being more explicit than the best explaination, here we go ...
Let's assume you want this output:
<TAG1000>
<TAG1100>
<TAG1110>
<TAG1111>20140127082427</TAG1111>
<TAG1112>15303.10</TAG1112>
</TAG1110>
<TAG1120>
<TAG1121>pol1</TAG1121>
</TAG1120>
</TAG1100>
<TAG1100>
<TAG1110>
<TAG1111>20140127082427</TAG1111>
<TAG1112>15303.20</TAG1112>
</TAG1110>
<TAG1120>
<TAG1121>pol2</TAG1121>
</TAG1120>
</TAG1100>
</TAG1000>
Here you should declare the tags in following order in your SELECT clause:
<TAG1100>, <TAG1110>, <TAG1111>, <TAG1112>, <TAG1120>, <TAG1121>
and don't forget to declare the FROM clause as well.
I hope this is clear enough and it will help some folks out there.
🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply