Conditionally selecting fields from xlm to table

Question

Post reply

Conditionally selecting fields from xlm to table

p.matejka

SSC Enthusiast

Points: 141
More actions
September 1, 2014 at 2:36 am

#97217

I have xml with fields which are identified <QUALF> tags (see example below).
<?xml version="1.0" encoding="iso-8859-2"?>
<ZINVOIC3>
<IDOC BEGIN="1">
<E1EDK02 SEGMENT="1">
<QUALF>009</QUALF>
<BELNR>1012833285</BELNR> 
</E1EDK02>
<E1EDK02 SEGMENT="1">
<QUALF>001</QUALF>
<BELNR>NO01140804</BELNR> 
</E1EDK02>
<E1EDP01 SEGMENT="1">
<MENGE>1.000</MENGE> 
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>80008932</IDTNR> 
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>8590669055883</IDTNR> 
</E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<MENGE>2.000</MENGE>
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>40008932</IDTNR> 
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>8590669055890</IDTNR> 
</E1EDP19>
</E1EDP01>
</IDOC>
</ZINVOIC3>
I need transform this data to flat structure (table) containing fields:
InvoiceNumber OrderNumber Quantity IdWare EanWare
1012833285 NO01140804 1.000 80008932 8590669055883
1012833285 NO01140804 2.000 40008932 8590669055890
Can anybody help me? 🙁

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

September 1, 2014 at 2:49 am

#1742181

OPENXML is one way of doing it. There are others.

John

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

I think that you are misusing the XML. You have the same elements' names for different information. The only way to know what is the real information is by reading the remarks. I do believe that there is a way to get the data that you want, but I don't know how to do it (I've never analyzed xml by the comments). Can you modify the XML, so each piece of information will have its own element's name?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

p.matejka SSC Enthusiast Points: 141 More actions · Answer 3

It's exactly my problem.

The nodes are the same (<E1EDK02 SEGMENT="1"> in the header of invoice for example), but the information about type of field are stored in the fields QUALF

QUALF = 009 = InvoiceNumber

QUALF = 001 = OrderNumber

The comments are not inluded in the original file, I aded it to example only for better udersunderstanding structure of xml.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Well, that changes the situation:-). Bellow is an Xquery that I think that it does what you need. Have a look and let me know

declare @xml xml =

'<?xml version="1.0" encoding="iso-8859-2"?>

<ZINVOIC3>

<IDOC BEGIN="1">

<E1EDK02 SEGMENT="1">

<QUALF>009</QUALF>

<BELNR>1012833285</BELNR> 

</E1EDK02>

<E1EDK02 SEGMENT="1">

<QUALF>001</QUALF>

<BELNR>NO01140804</BELNR> 

</E1EDK02>

<E1EDP01 SEGMENT="1">

<MENGE>1.000</MENGE> 

<E1EDP19 SEGMENT="1">

<QUALF>002</QUALF>

<IDTNR>80008932</IDTNR> 

</E1EDP19>

<E1EDP19 SEGMENT="1">

<QUALF>003</QUALF>

<IDTNR>8590669055883</IDTNR> 

</E1EDP19>

</E1EDP01>

<E1EDP01 SEGMENT="1">

<MENGE>2.000</MENGE>

<E1EDP19 SEGMENT="1">

<QUALF>002</QUALF>

<IDTNR>40008932</IDTNR> 

</E1EDP19>

<E1EDP19 SEGMENT="1">

<QUALF>003</QUALF>

<IDTNR>8590669055890</IDTNR> 

</E1EDP19>

</E1EDP01>

</IDOC>

</ZINVOIC3>'

--select @xml

select --tbl.x.query('.'),

tbl.x.value('(../E1EDK02[QUALF="009"]/BELNR)[1]','varchar(20)') as InvoiceNumber,

tbl.x.value('(../E1EDK02[QUALF="001"]/BELNR)[1]','varchar(20)') as OrderNumber,

tbl.x.value('(MENGE)[1]', 'real') as Quantity,

tbl.x.value('(E1EDP19[QUALF="002"]/IDTNR)[1]', 'int') as IdWare,

tbl.x.value('(E1EDP19[QUALF="003"]/IDTNR)[1]', 'bigint') as EanWare

from @xml.nodes('ZINVOIC3/IDOC/E1EDP01') tbl (x)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

p.matejka SSC Enthusiast Points: 141 More actions · Answer 5

Thank you very much, Adi.

It seems to be the right, what I need. 😀