Export child elements from XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Export child elements from XML

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2011-10-11 : 15:21:24

Hi all,
I have XML file that look like this
"<XBRoute>
<tid>8</tid>
<alias>20110606</alias>
<digitMatch>*620</digitMatch>
<extension>1</extension>
<policy>top_down</policy>
<rd>
<rdid>50924</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>50917</rdid>
<con_Routing>yes</con_Routing>
</rd>
</XBRoute>
"

And I use this query to import XML into SQL Table:

DECLARE @X XML
SELECT
@X = DMT
FROM OPENROWSET (BULK 'c:\Work\file.XML', SINGLE_BLOB) AS Import(DMT)

Select
,[XBRoute].query('alias').value('.','varchar(100)') AS alias
,[XBRoute].query('tid').value('.','varchar(100)') AS tid
,[XBRoute].query('rd/rdid').value('.','varchar(100)') AS rd
From @X.nodes('/XBRouteList/XBRoute') DMT([XBRoute])

I could have from 0 to 8 <rdid> elements for each <rd> parent element.
The query I have now just dumps all <rdid> values in one column, so in this example I get 5092450917

How I can get 2 columns with 50924 in the first column and 50917 in the second?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 02:01:49

see example illustration below


declare @x xml
set @x='
<XBRouteList>
<XBRoute>
<tid>8</tid>
<alias>20110606</alias>
<digitMatch>*620</digitMatch>
<extension>1</extension>
<policy>top_down</policy>
<rd>
<rdid>50924</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>50917</rdid>
<con_Routing>yes</con_Routing>
</rd>
</XBRoute>
<XBRoute>
<tid>6</tid>
<alias>20110313</alias>
<digitMatch>*345</digitMatch>
<extension>23</extension>
<policy>top_down</policy>
<rd>
<rdid>436456</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>3456235</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>263435</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>345642</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>324646</rdid>
<con_Routing>yes</con_Routing>
</rd>
</XBRoute>
<XBRoute>
<tid>12</tid>
<alias>20101206</alias>
<digitMatch>*566</digitMatch>
<extension>1</extension>
<policy>top_down</policy>
<rd>
<rdid>23415</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>3463677</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>3476457</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>547547</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>12445</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>346342</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>34534656</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>56865</rdid>
<con_Routing>yes</con_Routing>
</rd>
</XBRoute>
<XBRoute>
<tid>8</tid>
<alias>20110606</alias>
<digitMatch>*620</digitMatch>
<extension>1</extension>
<policy>top_down</policy>
<rd>
<rdid>50924</rdid>
<con_Routing>yes</con_Routing>
</rd>
<rd>
<rdid>50917</rdid>
<con_Routing>yes</con_Routing>
</rd>
</XBRoute>
</XBRouteList>'

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY alias,tid ORDER BY rd) AS Rn,alias,tid,rd
FROM
(
Select 
[XBRoute].value('alias[1]','varchar(100)') AS alias
,[XBRoute].value('tid[1]','varchar(100)') AS tid,
u.value('.','int') AS rd
From @X.nodes('/XBRouteList/XBRoute') DMT([XBRoute])
cross apply XBRoute.nodes('rd/rdid') t(u) 
)t
)
SELECT *
FROM CTE c
PIVOT(MAX(rd) FOR Rn in ([1],[2],[3],[4],[5],[6],[7],))p


output
---------------------------------------------------------
alias	        tid	1	2	3	4	5	6	7	8
20101206	12	12445	23415	56865	346342	547547	3463677	3476457	34534656
20110313	6	263435	324646	345642	436456	3456235	NULL	NULL	NULL
20110606	8	50917	50917	50924	50924	NULL	NULL	NULL	NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources