xml

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  June 15, 2017 at 2:12 pm

  #337668

  i have a following xml and expected o/p is given below. save xml as test.xml in your local , i have given code i have tired data is mixed for subnode(val description).

  <NodeA >
<row>
  <value Description="Product Description">amazon echo</value>
  <value Description="Productid">q24fw45-245</value>
  <value Description="Dealer Sale">
  <Val Description="Retail Price">$49</Val>
  <Val Description="Commission">12%</Val>
  </value>
  <value Description="ROR">
  <Val Description="Retail Price">$675</Val>
  <Val Description="Commission">15%</Val>
  </value>
</row>
<row>
  <value Description="Product Description">phone 7</value>
  <value Description="Productid">ab450-34</value>
  <value Description="Dealer Sale">
  <Val Description="Retail Price">$780</Val>
  <Val Description="Commission">18%</Val>
  </value>
  <value Description="ROR">
  <Val Description="Retail Price">$935</Val>
  <Val Description="Commission">28%</Val>
  </value>
</row>
</NodeA >

  Product Description	Productid	Dealer sale_Retail Price	Dealer sale_Commission	ROR_Retail Price	ROR_Commission
  amazon echo	q24fw45-245	$49	12%	$675	15%
  phone 7	ab450-34	$780	18%	$935	28%

  IF OBJECT_ID('tempdb..#urlset') IS NOT NULL
drop table #urlset
CREATE TABLE #UrlSet(
[Tip] [smallint] NOT NULL,
[xml] NULL
)

  declare @xml xml
set @xml = ''

  insert #UrlSet-- select 1, @xml
SELECT 1,CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK 'D:\test.xml', SINGLE_BLOB) AS x;

  select * from #UrlSet

  SELECT distinct e.value('@Description', 'varchar(100)') AS [Description]
    ,e.value('.', 'varchar(100)') AS value
    , DENSE_RANK() OVER (ORDER BY data) AS unique_b_node
FROM #UrlSet d
CROSS APPLY d.url.nodes('/NodeA/row') x1([data])
CROSS APPLY x1.data.nodes('/NodeA/row/value') x2(e)


• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  June 16, 2017 at 2:26 am

  #1947053

  This should get you passed this hurdle
  😎

  IF OBJECT_ID('tempdb..#urlset') IS NOT NULL
drop table #urlset
CREATE TABLE #UrlSet(
[Tip] [smallint] NOT NULL,
[xml] NULL
);
declare @TXML xml = '<NodeA>
<row>
  <value Description="Product Description">amazon echo</value>
  <value Description="Productid">q24fw45-245</value>
  <value Description="Dealer Sale">
  <Val Description="Retail Price">$49</Val>
  <Val Description="Commission">12%</Val>
  </value>
  <value Description="ROR">
  <Val Description="Retail Price">$675</Val>
  <Val Description="Commission">15%</Val>
  </value>
</row>
<row>
  <value Description="Product Description">phone 7</value>
  <value Description="Productid">ab450-34</value>
  <value Description="Dealer Sale">
  <Val Description="Retail Price">$780</Val>
  <Val Description="Commission">18%</Val>
  </value>
  <value Description="ROR">
  <Val Description="Retail Price">$935</Val>
  <Val Description="Commission">28%</Val>
  </value>
</row>
</NodeA>';

  INSERT INTO #UrlSet(Tip,url) VALUES (1,@TXML)

  SELECT
    UX.Tip
 ,XURL.DATA.value('(value[@Description="Product Description"]/text())[1]','varchar(50)') AS [Product Description]
 ,XURL.DATA.value('(value[@Description="Productid"]/text())[1]','varchar(50)')    AS [Productid]
 ,DEALER.DATA.value('(Val[@Description="Retail Price"]/text())[1]','varchar(50)')   AS [Dealer Retail Price]
 ,DEALER.DATA.value('(Val[@Description="Commission"]/text())[1]','varchar(50)')    AS [Dealer Commission]
 ,DEALER.DATA.value('(Val[@Description="Retail Price"]/text())[1]','varchar(50)')   AS [ROR Retail Price]
 ,ROR.DATA.value('(Val[@Description="Commission"]/text())[1]','varchar(50)')     AS [ROR Commission]
FROM    #UrlSet        UX
CROSS APPLY UX.url.nodes('/NodeA/row')          XURL(DATA)
CROSS APPLY XURL.DATA.nodes('value[@Description="Dealer Sale"]') DEALER(DATA)
CROSS APPLY XURL.DATA.nodes('value[@Description="ROR"]')    ROR(DATA);

  Output
  

  Tip    Product Description  Productid    Dealer Retail Price  Dealer Commission  ROR Retail Price  ROR Commission
------ -------------------- ------------ -------------------- ------------------ ----------------- ---------------
1      amazon echo          q24fw45-245  $49                  12%                $49               15%
1      phone 7              ab450-34     $780                 18%                $780              28%