1

Topic: Loading of a plural element in the table

Is available XML the document, with the given structure

<doc>
<n_doc> -01-01-000272 </n_doc>
<d_start> 25.02.2015 </d_start>
<date_e> 31.12.2099 </date_e>
<d_term> 22.07.2016 </d_term>
<mp> 13 </mp>
<mp> 31 </mp>
<mp> 32 </mp>
</doc>

I interpose into the table a data sampling, received by following request

select
n_doc;
convert (datetime, case when d_start = ' 0 ' or d_start = "then null else d_start end, 104) as d_start;
mp
from openxml (@idoc, '/packet/medCompany/doc ', 1)
with (
n_doc varchar (32) ' n_doc ';
d_start varchar (10) ' d_start ';
mp varchar (4) ' mp'
)
where n_doc is not null

On an output I receive single line with the first element mp -01-01-000272, 2/25/2015, 13
Prompt how to receive, please, rowset with all elements mp -01-01-000272, 2/25/2015, 13
-01-01-000272, 2/25/2015, 31
-01-01-000272, 2/25/2015, 32

2

Re: Loading of a plural element in the table

1. Manually to disassemble contents edge table - examples are here: https://docs.microsoft.com/en-us/sql/re … g-openxml.
2. Instead of openxml to use xquery:

declare
@x xml=N'
<doc>
<n_doc> -01-01-000272 </n_doc>
<d_start> 25.02.2015 </d_start>
<date_e> 31.12.2099 </date_e>
<d_term> 22.07.2016 </d_term>
<mp> 13 </mp>
<mp> 31 </mp>
<mp> 32 </mp>
</doc>
<doc>
<n_doc> -01-01-000273 </n_doc>
<d_start> 25.02.2016 </d_start>
<date_e> 31.12.2098 </date_e>
<d_term> 22.07.2017 </d_term>
<mp> 131 </mp>
<mp> 311 </mp>
<mp> 321 </mp>
</doc>
';
;with cte as (
select
t.n.value (' n_doc [1] ', ' varchar (32) ') n_doc;
t.n.value (' d_start [1] ', ' varchar (10) ') d_start;
t.n.query (' mp ') mp
from @x.nodes ('/doc ') t (n)
)
select
e.n_doc;
e.d_start;
t.n.value ('. ', ' varchar (4) ')
from cte e
cross apply e.mp.nodes (' mp ') t (n)