Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conditionally selecting fields from xlm to table Expand / Collapse
Author
Message
Posted Monday, September 1, 2014 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:52 AM
Points: 3, Visits: 43
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? :-(
Post #1609276
Posted Monday, September 1, 2014 2:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,386, Visits: 9,962
OPENXML is one way of doing it. There are others.

John
Post #1609285
Posted Monday, September 1, 2014 5:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1609319
Posted Monday, September 1, 2014 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:52 AM
Points: 3, Visits: 43
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.
Post #1609324
Posted Monday, September 1, 2014 6:47 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1609339
Posted Monday, September 1, 2014 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:52 AM
Points: 3, Visits: 43
Thank you very much, Adi.
It seems to be the right, what I need.
Post #1609345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse