Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditionally selecting fields from xlm to table


Conditionally selecting fields from xlm to table

Author
Message
p.matejka
p.matejka
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 68
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> <!-- InvoiceNumber -->
</E1EDK02>
<E1EDK02 SEGMENT="1">
<QUALF>001</QUALF>
<BELNR>NO01140804</BELNR> <!-- OrderNumber -->
</E1EDK02>
<E1EDP01 SEGMENT="1">
<MENGE>1.000</MENGE> <!-- Quantity -->
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>80008932</IDTNR> <!-- IdWare -->
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>8590669055883</IDTNR> <!-- EanWare -->
</E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<MENGE>2.000</MENGE>
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>40008932</IDTNR> <!-- IdWare -->
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>8590669055890</IDTNR> <!-- EanWare-->
</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? :-(
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7414 Visits: 15114
OPENXML is one way of doing it. There are others.

John
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 6076
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
p.matejka
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 68
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
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 6076
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> <!-- InvoiceNumber -->
</E1EDK02>
<E1EDK02 SEGMENT="1">
<QUALF>001</QUALF>
<BELNR>NO01140804</BELNR> <!-- OrderNumber -->
</E1EDK02>
<E1EDP01 SEGMENT="1">
<MENGE>1.000</MENGE> <!-- Quantity -->
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>80008932</IDTNR> <!-- IdWare -->
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>8590669055883</IDTNR> <!-- EanWare -->
</E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<MENGE>2.000</MENGE>
<E1EDP19 SEGMENT="1">
<QUALF>002</QUALF>
<IDTNR>40008932</IDTNR> <!-- IdWare -->
</E1EDP19>
<E1EDP19 SEGMENT="1">
<QUALF>003</QUALF>
<IDTNR>8590669055890</IDTNR> <!-- EanWare-->
</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
p.matejka
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 68
Thank you very much, Adi.
It seems to be the right, what I need. :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search