sql server 2008 - XML Path Loop Inline -

i utilizing sparsely populated version of tcp-h database attempting output data orders using xml path. have query find each supplier , part supply, aggregated numbers orders , wish include list of every orders info under single orders element. can in oracle single query looks like

<orderedpart>    <partname partkey="100">part#100</partname>    <suppliername supplierkey="1">supplier#1</suppliername>    <totalqty>32</totalqty>    <ordercount>2</ordercount>    <orders>      <order orderkey="4928">        <orderdate>1993-10-04</orderdate>        <orderqty>4</orderqty>      </order>      <order orderkey="1223">        <orderdate>1996-05-25</orderdate>        <orderqty>28</orderqty>      </order>    </orders>  </orderedpart>

however, in current sql server 2008 query current outputting them separately like

<orderedpart>    <partname partkey="100">part#100</partname>    <suppliername supplierkey="1">supplier#1</suppliername>    <totalqty>32</totalqty>    <ordercount>2</ordercount>    <orders>      <order orderkey="4928">        <orderdate>1993-10-04</orderdate>        <orderqty>4</orderqty>      </order>    </orders>  </orderedpart>  <orderedpart>    <partname partkey="100">part#100</partname>    <suppliername supplierkey="1">supplier#1</suppliername>    <totalqty>32</totalqty>    <ordercount>2</ordercount>    <orders>      <order orderkey="1223">        <orderdate>1996-05-25</orderdate>        <orderqty>28</orderqty>      </order>    </orders>  </orderedpart>

in oracle using xmlagg works fine have not been able find equivalent in sql server 2008 in searching. want within sql query ideally can in oracle without having process after scripts. query is

select l.l_partkey 'partname/@partkey', p.p_name partname, l.l_suppkey 'suppliername/@supplierkey', s.s_name suppliername,  (select sum(d.l_quantity)  	from lineitem d  	where d.l_partkey = p.p_partkey , d.l_suppkey = s.s_suppkey  	group l_partkey) totalqty,  (select count(d.l_orderkey)  	from lineitem d  	where d.l_partkey = l.l_partkey , d.l_suppkey = l.l_suppkey  	group d.l_partkey) ordercount,    (select l.l_orderkey '@orderkey', o.o_orderdate orderdate, l.l_quantity orderqty   	where l.l_partkey = p.p_partkey , l.l_suppkey = s.s_suppkey , l.l_orderkey = o.o_orderkey  	order o.o_orderdate desc  	for xml path('order'), root('orders'), type)  	  lineitem l, supplier s, part p, orders o  l.l_partkey = p.p_partkey , l.l_suppkey = s.s_suppkey , l.l_orderkey = o.o_orderkey  order p.p_name asc  xml path('orderedpart'), root('orderedparts'), type;

consider 2 approaches work together:

1. an xslt script since require xml transformation. sql (a special-purpose declarative programming language), xslt special-purpose declarative language used style, transform, format xml files in various end-use structures.
2. a general purpose language java, c#, php, python, vb call xslt; database agnostic solution , not restricted rdms (oracle, sql server, postgre, mysql, etc.); also, general purpose languages can connect rdms using odbc/oledb drivers import needed data. see below example scripts.

as mentioned, 2 above approaches work great every general-purpose language maintains xslt processor. needs, need group orders partname node. in xslt 1.0, can use muenchian method.

xslt script (to saved externally .xsl file)

<xsl:transform xmlns:xsl="http://www.w3.org/1999/xsl/transform" version="1.0"> <xsl:output version="1.0" encoding="utf-8" indent="yes" />  <xsl:key name="orderid" match="orderedpart" use="partname" />    <xsl:template match="root">    <orderedpart>     <xsl:for-each select="orderedpart[count(. | key('orderid', partname)[1]) = 1]">       <xsl:copy-of select="partname"/>       <xsl:copy-of select="suppliername"/>       <xsl:copy-of select="totalqty"/>       <xsl:copy-of select="ordercount"/>        <xsl:for-each select="key('orderid', partname)">               <xsl:copy-of select="orders"/>              </xsl:for-each>     </xsl:for-each>    </orderedpart>   </xsl:template>     </xsl:transform> 

python script transform (using lxml module)

import os import lxml.etree et  cd = os.path.dirname(os.path.abspath(__file__))      dom = et.parse(os.path.join(cd, 'original.xml')) xslt = et.parse(os.path.join(cd, 'transform.xsl'))  transform = et.xslt(xslt) newdom = transform(dom)  tree_out = et.tostring(newdom, encoding='utf-8', pretty_print=true,  xml_declaration=true)  xmlfile = open(os.path.join(cd, 'final.xml'),'wb') xmlfile.write(tree_out) xmlfile.close() 

php script transform (using xsl extension)

$cd = dirname(__file__);  $xml = new domdocument('1.0', 'utf-8'); $xml->load($cd.'/original.xml');  $xslfile = new domdocument('1.0', 'utf-8'); $xslfile->load($cd.'/transform.xsl');  // tranform xml xslt $proc = new xsltprocessor; $proc->importstylesheet($xslfile);  $newxml = $proc->transformtoxml($xml);  // save output file $finalxml = $cd.'/final.xml'; file_put_contents($finalxml, $newxml); 

vba script transform (use in ms excel macro or ms access module using msxml object)

dim xmldoc new msxml2.domdocument  dim xsldoc new msxml2.domdocument dim newdoc new msxml2.domdocument  xmldoc.load activeworkbook.path & "\original.xml"     xsldoc.load activeworkbook.path & "\transform.xsl"  xmldoc.transformnodetoobject xsldoc, newdoc newdoc.save activeworkbook.path & "\final.xml" 

r script transform (unfortunately date, r not have dedicated cran xslt library pc users r can use msxml object via rdcomclient package, see fellow user hrbrmstr's git project)

library(rdcomclient) setwd("c:\\path\\to\\working\\directory")  xmlfile = comcreate("msxml2.domdocument") xslfile = comcreate("msxml2.domdocument") newxmlfile = comcreate("msxml2.domdocument")  xmlfile.async = false xmlfile$load("original.xml")  xslfile.async = false xslfile$load("transform.xsl")  xmlfile$transformnodetoobject(xslfile, newxmlfile) newxmlfile$save("final.xml") 

sas script transform (using proc xsl uses open-source xalan-java xslt processor)

proc xsl      in="c:\path\to\original.xml"     xsl="c:\path\to\transform.xsl"     out="c:\path\to\final.xml"; run; 

still other examples in java, c#, perl, etc. above examples future readers' references.

eventual output

<?xml version='1.0' encoding='utf-8'?> <orderedpart>   <partname partkey="100">part#100</partname>   <suppliername supplierkey="1">supplier#1</suppliername>   <totalqty>32</totalqty>   <ordercount>2</ordercount>   <orders>     <order orderkey="4928">       <orderdate>1993-10-04</orderdate>       <orderqty>4</orderqty>     </order>   </orders>   <orders>     <order orderkey="1223">       <orderdate>1996-05-25</orderdate>       <orderqty>28</orderqty>     </order>   </orders> </orderedpart>