XML to SQL tables
-
Hi There,
I have some XML data stored in a column of a table.
http://www.sqlservercentral.com/Forums/Attachment18833.aspx%5B/img%5D
There are lot of same kind of xml rows are stored in a table. I need to convert all those xml data into table structure. what is the best way to do that(Set based!!!) ?
Problem Description:
1.There are 4 tables burried inside the xml,
> SourceSystem
> TransactionHeader
> TransactionConsumer
> TransactionDetail
2. Left join the TransactionDetail table with all the other tables
Sample XML:
<?xml version="1.0"?><ConsumerTransactionList>
<ConsumerTransaction>
<SourceSystem>
<SourceTimestamp>4/1/2016</SourceTimestamp>
<MarketCode>ABC</MarketCode>
</SourceSystem>
<TransactionHeader>
<T_Timestamp>4/27/2016</T_Timestamp>
<PersonnelCode>1234</PersonnelCode>
<TransactionConsumer>
<ConsumerId>92723</ConsumerId>
</TransactionConsumer>
</TransactionHeader>
<TransactionDetailList>
<TransactionDetail>
<LineNum>1</LineNum>
<TypeCode>X</TypeCode>
<PersonnelCode>9568</PersonnelCode>
</TransactionDetail>
<TransactionDetail>
<LineNum>2</LineNum>
<TypeCode>Y</TypeCode>
<PersonnelCode>2583</PersonnelCode>
</TransactionDetail>
</TransactionDetailList>
</ConsumerTransactionList>
</ConsumerTransaction>
Thanks in advance
-
-- sample table
DECLARE @demo TABLE
(
SomeID int identity, -- you need this to know the source of your XML
SomeXML XML
);
-- insert a couple xml values
INSERT @demo
VALUES
('<?xml version="1.0"?>
<ConsumerTransactionList>
<ConsumerTransaction>
<SourceSystem>
<SourceTimestamp>6/17/2016</SourceTimestamp>
<MarketCode>XXXDDD</MarketCode>
</SourceSystem>
<TransactionHeader>
<T_Timestamp>2/2/2013</T_Timestamp>
<PersonnelCode>6455</PersonnelCode>
<TransactionConsumer>
<ConsumerId>92723</ConsumerId>
</TransactionConsumer>
</TransactionHeader>
<TransactionDetailList>
<TransactionDetail>
<LineNum>1</LineNum>
<TypeCode>X</TypeCode>
<PersonnelCode>9568</PersonnelCode>
</TransactionDetail>
<TransactionDetail>
<LineNum>2</LineNum>
<TypeCode>YY</TypeCode>
<PersonnelCode>2583</PersonnelCode>
</TransactionDetail>
</TransactionDetailList>
</ConsumerTransaction>
</ConsumerTransactionList>'),
('<?xml version="1.0"?>
<ConsumerTransactionList>
<ConsumerTransaction>
<SourceSystem>
<SourceTimestamp>4/1/2016</SourceTimestamp>
<MarketCode>ABC</MarketCode>
</SourceSystem>
<TransactionHeader>
<T_Timestamp>4/27/2016</T_Timestamp>
<PersonnelCode>1234</PersonnelCode>
<TransactionConsumer>
<ConsumerId>92723</ConsumerId>
</TransactionConsumer>
</TransactionHeader>
<TransactionDetailList>
<TransactionDetail>
<LineNum>1</LineNum>
<TypeCode>X</TypeCode>
<PersonnelCode>9568</PersonnelCode>
</TransactionDetail>
<TransactionDetail>
<LineNum>2</LineNum>
<TypeCode>Y</TypeCode>
<PersonnelCode>2583</PersonnelCode>
</TransactionDetail>
</TransactionDetailList>
</ConsumerTransaction>
</ConsumerTransactionList>');
SELECT
SomeID, -- you need this to know where your record came from
SourceTimestamp =
x1.x.value('(ConsumerTransaction/SourceSystem/SourceTimestamp/text())[1]','varchar(20)'),
MarketCode =
x1.x.value('(ConsumerTransaction/SourceSystem/MarketCode/text())[1]','varchar(20)'),
TransactionTimeStamp =
x1.x.value('(ConsumerTransaction/TransactionHeader/T_Timestamp/text())[1]','varchar(20)'),
PersonnelCode =
x1.x.value('(ConsumerTransaction/TransactionHeader/PersonnelCode/text())[1]','varchar(20)'),
ConsumerID =
x1.x.value('(ConsumerTransaction/TransactionHeader/TransactionConsumer/ConsumerId/text())[1]','varchar(20)'),
LineNum =
x2.x.value('(LineNum/text())[1]','varchar(20)'),
TransactionTypeCode =
x2.x.value('(TypeCode/text())[1]','varchar(20)'),
PersonnelCode =
x1.x.value('(ConsumerTransaction/TransactionHeader/PersonnelCode/text())[1]','varchar(20)')
FROM @demo d
CROSS APPLY d.SomeXML.nodes('ConsumerTransactionList') x1(x)
CROSS APPLY x1.x.nodes('//TransactionDetail') x2(x)
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
any set based approach? with out using cross apply ?
-
squvi.87 (4/28/2016)
any set based approach? with out using cross apply ?That's the "lovely" part of XML... Cross Apply IS the set-based approach.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
squvi.87 (4/28/2016)
any set based approach? with out using cross apply ?XML is a different animal...
When did CROSS APPLY become the "not set based" approach?
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Alan.B (4/28/2016)
squvi.87 (4/28/2016)
any set based approach? with out using cross apply ?XML is a different animal...
When did CROSS APPLY become the "not set based" approach?
A lot of people incorrectly consider CROSS APPLY to be RBAR because it behaves like a correlated subquery when single row returns are produced by CROSS APPLY. Of course, a correlated subquery is also not necessarily RBAR either, especially when an equality is the nature of the subquery.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (4/28/2016)
A lot of people incorrectly consider CROSS APPLY to be RBAR because it behaves like a correlated subquery when single row returns are produced by CROSS APPLY.It is, actually, RBAR.
Well optimised RBAR, because it's done not on SQL level, but still RBAR.
I deal currently with relatively big data sets, and every time I carelessly use "easy" CROSS APPLY instead INNER JOIN (which would require some non-trivial elaborations) I feel the difference instantly.
Since I don't use (NOLOCK) I have to kill CROSS APPLY queries before they manage to finish not to block live database.
No such problems with INNER JOINs.
_____________
Code for TallyGenerator -
Sergiy (4/28/2016)
Jeff Moden (4/28/2016)
A lot of people incorrectly consider CROSS APPLY to be RBAR because it behaves like a correlated subquery when single row returns are produced by CROSS APPLY.It is, actually, RBAR.
Well optimised RBAR, because it's done not on SQL level, but still RBAR.
I deal currently with relatively big data sets, and every time I carelessly use "easy" CROSS APPLY instead INNER JOIN (which would require some non-trivial elaborations) I feel the difference instantly.
Since I don't use (NOLOCK) I have to kill CROSS APPLY queries before they manage to finish not to block live database.
No such problems with INNER JOINs.
I've not experienced that... heh... mostly because I use an INNER JOIN instead. 😛 I'll have to give it a try. Thanks for the heads up.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sergiy (4/28/2016)
Jeff Moden (4/28/2016)
A lot of people incorrectly consider CROSS APPLY to be RBAR because it behaves like a correlated subquery when single row returns are produced by CROSS APPLY.It is, actually, RBAR.
Well optimised RBAR, because it's done not on SQL level, but still RBAR.
It's expensive, especially when you look at the number of lob reads, but it's the fastest way to parse XML data using the SQL engine that I'm aware of.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Alan.B (4/28/2016)
It's expensive, especially when you look at the number of lob reads, but it's the fastest way to parse XML data using the SQL engine that I'm aware of.XML message is a hierarchical, multilayered data storage driven by human-oriented mark-up language.
What would you expect?
Comparing to parsing XML string itself any CROSS APPLY is lightening fast.
I suspect, even a cursor would not slow the process too much. 🙂
_____________
Code for TallyGenerator -
Any example driven links to learn the xml querying techniques?
-
squvi.87 (4/29/2016)
Any example driven links to learn the xml querying techniques?I'll second that !
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply