SSIS - How to retrieve all the elements of ROOT using XML Task
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
So, I have an XML file that contains an order and for which I am using a Data Flow Task in order to extract the information and insert them into some tables in MSSQL. So far, so good. However, there are some information that I cannot reach to using this method and I need to use an XML Task for this:
But, I would like to extract several information, not just one field. How can I achieve this and use it further to insert them into a table? My XML looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description/>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
</OrderLine>
<OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
</OrderLine>
</Order>
sql-server xml ssis edited Nov 24 '18 at 14:10
Larnu
23.1k51933
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
|
show 5 more comments
1
So, I have an XML file that contains an order and for which I am using a Data Flow Task in order to extract the information and insert them into some tables in MSSQL. So far, so good. However, there are some information that I cannot reach to using this method and I need to use an XML Task for this:
But, I would like to extract several information, not just one field. How can I achieve this and use it further to insert them into a table? My XML looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description/>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
</OrderLine>
<OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
</OrderLine>
</Order>
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
Honestly, I find the xml reader in SSIS awful. From personal experience I find it far easier to import the raw xml into SQL Server and then using XQUERY to do the work.
– Larnu
Nov 24 '18 at 14:10
How can I do this? Is going to always another file with another name so basically I need to take all the files from a specific folder with the.xmlextension.
– Codrin Mihail Afrasinei
Nov 24 '18 at 14:14
If you need to do an operation for each object, you need to use an (appropriately named) For Each Loop Container; which can loop on files in a directory (and recurse if needed).
– Larnu
Nov 24 '18 at 14:16
I know this withFor Each Loop. But I also need to extract some info from the root element.
– Codrin Mihail Afrasinei
Nov 24 '18 at 14:17
Yes, see my original comment.
– Larnu
Nov 24 '18 at 14:18
|
show 5 more comments
1
1
1
So, I have an XML file that contains an order and for which I am using a Data Flow Task in order to extract the information and insert them into some tables in MSSQL. So far, so good. However, there are some information that I cannot reach to using this method and I need to use an XML Task for this:
But, I would like to extract several information, not just one field. How can I achieve this and use it further to insert them into a table? My XML looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description/>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
</OrderLine>
<OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
</OrderLine>
</Order>
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
So, I have an XML file that contains an order and for which I am using a Data Flow Task in order to extract the information and insert them into some tables in MSSQL. So far, so good. However, there are some information that I cannot reach to using this method and I need to use an XML Task for this:
But, I would like to extract several information, not just one field. How can I achieve this and use it further to insert them into a table? My XML looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description/>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
</OrderLine>
<OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
</OrderLine>
</Order>
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
edited Nov 24 '18 at 14:10
Larnu
23.1k51933
23.1k51933
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
asked Nov 24 '18 at 14:06
Codrin Mihail AfrasineiCodrin Mihail Afrasinei
62
62
Honestly, I find the xml reader in SSIS awful. From personal experience I find it far easier to import the raw xml into SQL Server and then using XQUERY to do the work.
– Larnu
Nov 24 '18 at 14:10
How can I do this? Is going to always another file with another name so basically I need to take all the files from a specific folder with the.xmlextension.
– Codrin Mihail Afrasinei
Nov 24 '18 at 14:14
If you need to do an operation for each object, you need to use an (appropriately named) For Each Loop Container; which can loop on files in a directory (and recurse if needed).
– Larnu
Nov 24 '18 at 14:16
I know this withFor Each Loop. But I also need to extract some info from the root element.
– Codrin Mihail Afrasinei
Nov 24 '18 at 14:17
Yes, see my original comment.
– Larnu
Nov 24 '18 at 14:18
|
show 5 more comments
Honestly, I find the xml reader in SSIS awful. From personal experience I find it far easier to import the raw xml into SQL Server and then using XQUERY to do the work.
– Larnu
Nov 24 '18 at 14:10
How can I do this? Is going to always another file with another name so basically I need to take all the files from a specific folder with the.xmlextension.
– Codrin Mihail Afrasinei
Nov 24 '18 at 14:14
If you need to do an operation for each object, you need to use an (appropriately named) For Each Loop Container; which can loop on files in a directory (and recurse if needed).
– Larnu
Nov 24 '18 at 14:16
I know this withFor Each Loop. But I also need to extract some info from the root element.
– Codrin Mihail Afrasinei
Nov 24 '18 at 14:17
Yes, see my original comment.
– Larnu
Nov 24 '18 at 14:18
Honestly, I find the xml reader in SSIS awful. From personal experience I find it far easier to import the raw xml into SQL Server and then using XQUERY to do the work.
– Larnu
Nov 24 '18 at 14:10
Honestly, I find the xml reader in SSIS awful. From personal experience I find it far easier to import the raw xml into SQL Server and then using XQUERY to do the work.
– Larnu
Nov 24 '18 at 14:10
How can I do this? Is going to always another file with another name so basically I need to take all the files from a specific folder with the
.xml extension.– Codrin Mihail Afrasinei
Nov 24 '18 at 14:14
How can I do this? Is going to always another file with another name so basically I need to take all the files from a specific folder with the
.xml extension.– Codrin Mihail Afrasinei
Nov 24 '18 at 14:14
If you need to do an operation for each object, you need to use an (appropriately named) For Each Loop Container; which can loop on files in a directory (and recurse if needed).
– Larnu
Nov 24 '18 at 14:16
If you need to do an operation for each object, you need to use an (appropriately named) For Each Loop Container; which can loop on files in a directory (and recurse if needed).
– Larnu
Nov 24 '18 at 14:16
I know this with
For Each Loop. But I also need to extract some info from the root element.– Codrin Mihail Afrasinei
Nov 24 '18 at 14:17
I know this with
For Each Loop. But I also need to extract some info from the root element.– Codrin Mihail Afrasinei
Nov 24 '18 at 14:17
Yes, see my original comment.
– Larnu
Nov 24 '18 at 14:18
Yes, see my original comment.
– Larnu
Nov 24 '18 at 14:18
|
show 5 more comments
1 Answer
1
active
oldest
votes
0
First of all i changed your xml file to (Sql server was complaining about the tags..):
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description></Description>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
</BuyerCustomerParty>
</Order>
After doing that , you can access your data as :
Declare @fileData XML
Select @fileData=BulkColumn from OpenRowSet(Bulk'PathtoXMLXMLFileName.xml',Single_blob) x;
select
x.xData.value('ID[1]','int'),
x.xData.value('Price[1]','float'),
x.xData.value('Quantity[1]','int')
from @fileData.nodes('/Order/BuyerCustomerParty')
x(xData);
This for the /Order/BuyerCustomerParty node.. You can declare /Order/BuyerCustomerParty/Item as node and call x.xData.value('Description[1]','varChar(max)') for the Item description and so goes on...
Your select can be easily inserted in a temporary table , from which you can manage yoyr xml data.
Hope it helps!
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
Nice spot on theOrderLinenode that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.
– Larnu
Nov 24 '18 at 14:59
Well, maybe I missed to copy also theOrderLineenging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53458976%2fssis-how-to-retrieve-all-the-elements-of-root-using-xml-task%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
First of all i changed your xml file to (Sql server was complaining about the tags..):
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description></Description>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
</BuyerCustomerParty>
</Order>
After doing that , you can access your data as :
Declare @fileData XML
Select @fileData=BulkColumn from OpenRowSet(Bulk'PathtoXMLXMLFileName.xml',Single_blob) x;
select
x.xData.value('ID[1]','int'),
x.xData.value('Price[1]','float'),
x.xData.value('Quantity[1]','int')
from @fileData.nodes('/Order/BuyerCustomerParty')
x(xData);
This for the /Order/BuyerCustomerParty node.. You can declare /Order/BuyerCustomerParty/Item as node and call x.xData.value('Description[1]','varChar(max)') for the Item description and so goes on...
Your select can be easily inserted in a temporary table , from which you can manage yoyr xml data.
Hope it helps!
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
Nice spot on theOrderLinenode that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.
– Larnu
Nov 24 '18 at 14:59
Well, maybe I missed to copy also theOrderLineenging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
add a comment |
0
First of all i changed your xml file to (Sql server was complaining about the tags..):
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description></Description>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
</BuyerCustomerParty>
</Order>
After doing that , you can access your data as :
Declare @fileData XML
Select @fileData=BulkColumn from OpenRowSet(Bulk'PathtoXMLXMLFileName.xml',Single_blob) x;
select
x.xData.value('ID[1]','int'),
x.xData.value('Price[1]','float'),
x.xData.value('Quantity[1]','int')
from @fileData.nodes('/Order/BuyerCustomerParty')
x(xData);
This for the /Order/BuyerCustomerParty node.. You can declare /Order/BuyerCustomerParty/Item as node and call x.xData.value('Description[1]','varChar(max)') for the Item description and so goes on...
Your select can be easily inserted in a temporary table , from which you can manage yoyr xml data.
Hope it helps!
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
Nice spot on theOrderLinenode that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.
– Larnu
Nov 24 '18 at 14:59
Well, maybe I missed to copy also theOrderLineenging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
add a comment |
0
0
0
First of all i changed your xml file to (Sql server was complaining about the tags..):
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description></Description>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
</BuyerCustomerParty>
</Order>
After doing that , you can access your data as :
Declare @fileData XML
Select @fileData=BulkColumn from OpenRowSet(Bulk'PathtoXMLXMLFileName.xml',Single_blob) x;
select
x.xData.value('ID[1]','int'),
x.xData.value('Price[1]','float'),
x.xData.value('Quantity[1]','int')
from @fileData.nodes('/Order/BuyerCustomerParty')
x(xData);
This for the /Order/BuyerCustomerParty node.. You can declare /Order/BuyerCustomerParty/Item as node and call x.xData.value('Description[1]','varChar(max)') for the Item description and so goes on...
Your select can be easily inserted in a temporary table , from which you can manage yoyr xml data.
Hope it helps!
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
First of all i changed your xml file to (Sql server was complaining about the tags..):
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UBLVersionID>2.1</UBLVersionID>
<CustomizationID>ver2.0</CustomizationID>
<ProfileID>RO11588780_RO13965909</ProfileID>
<ID>00680733</ID>
<CopyIndicator>true</CopyIndicator>
<UUID>DX01_144_20181119_01006030</UUID>
<IssueDate>2018-11-19</IssueDate>
<OrderTypeCode>220</OrderTypeCode>
<Note>STOCAJ</Note>
<AccountingCostCode>32</AccountingCostCode>
<LineCountNumeric>16</LineCountNumeric>
<RequestedDeliveryPeriod>
<StartDate>2018-11-23</StartDate>
<EndDate>2018-11-23</EndDate>
<Description></Description>
</RequestedDeliveryPeriod>
<BuyerCustomerParty>
<ID>233</ID>
<Price CurrencyID="RON">
<Amount>40.85</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>HARRY POTTER 7</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527676</BuyersItemIdentification>
<StandardItemIdentification>9786067884159</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
<ID>234</ID>
<Price CurrencyID="RON">
<Amount>20.61</Amount>
</Price>
<Quantity UnitCode="PCE">
<Amount>2</Amount>
</Quantity>
<Item>
<Description>ARCUL DE ARAMA</Description>
<PackQuantity>1</PackQuantity>
<PackSizeNumeric>2</PackSizeNumeric>
<BuyersItemIdentification>32527677</BuyersItemIdentification>
<StandardItemIdentification>9786068811574</StandardItemIdentification>
</Item>
<OrderLine>
</OrderLine>
</BuyerCustomerParty>
</Order>
After doing that , you can access your data as :
Declare @fileData XML
Select @fileData=BulkColumn from OpenRowSet(Bulk'PathtoXMLXMLFileName.xml',Single_blob) x;
select
x.xData.value('ID[1]','int'),
x.xData.value('Price[1]','float'),
x.xData.value('Quantity[1]','int')
from @fileData.nodes('/Order/BuyerCustomerParty')
x(xData);
This for the /Order/BuyerCustomerParty node.. You can declare /Order/BuyerCustomerParty/Item as node and call x.xData.value('Description[1]','varChar(max)') for the Item description and so goes on...
Your select can be easily inserted in a temporary table , from which you can manage yoyr xml data.
Hope it helps!
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
answered Nov 24 '18 at 14:47
HelgatoHelgato
1415
1415
Nice spot on theOrderLinenode that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.
– Larnu
Nov 24 '18 at 14:59
Well, maybe I missed to copy also theOrderLineenging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
add a comment |
Nice spot on theOrderLinenode that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.
– Larnu
Nov 24 '18 at 14:59
Well, maybe I missed to copy also theOrderLineenging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
Nice spot on the
OrderLine node that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.– Larnu
Nov 24 '18 at 14:59
Nice spot on the
OrderLine node that is ended before one is opened. If that, however, is how the OP's data looks then that does mean they need to fix (all of) their XML so that it's valid.– Larnu
Nov 24 '18 at 14:59
Well, maybe I missed to copy also the
OrderLine enging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
Well, maybe I missed to copy also the
OrderLine enging tag. Before I need to do all this, I have to import the raw xml data into a table, using a For Each Loop.– Codrin Mihail Afrasinei
Nov 24 '18 at 15:01
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
It has multiple errors on tags , not just this. </BuyerCustomerParty> is missing also , for example. I think i inserted 3-4 tags.. You can use also , a header table and a detail one (for the items) and insert data with the INSERT...SELECT statements, without using Loop. We dont know your exact purpose , so we speak hypothetically.
– Helgato
Nov 24 '18 at 15:09
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
Please have in mind that I just mistype, that's all.
– Codrin Mihail Afrasinei
Nov 24 '18 at 15:12
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53458976%2fssis-how-to-retrieve-all-the-elements-of-root-using-xml-task%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Honestly, I find the xml reader in SSIS awful. From personal experience I find it far easier to import the raw xml into SQL Server and then using XQUERY to do the work.
– Larnu
Nov 24 '18 at 14:10
How can I do this? Is going to always another file with another name so basically I need to take all the files from a specific folder with the
.xmlextension.– Codrin Mihail Afrasinei
Nov 24 '18 at 14:14
If you need to do an operation for each object, you need to use an (appropriately named) For Each Loop Container; which can loop on files in a directory (and recurse if needed).
– Larnu
Nov 24 '18 at 14:16
I know this with
For Each Loop. But I also need to extract some info from the root element.– Codrin Mihail Afrasinei
Nov 24 '18 at 14:17
Yes, see my original comment.
– Larnu
Nov 24 '18 at 14:18