Sql Server 2008R2 XML导出(Sql Server 2008R2 XML export)

 将数据从数据库导出到XML时遇到一些问题。 我可能已经过头了，因为我没有早期的XML知识但嘿，它有多难啊:) 
 
 我们已经通过excel表发送了这些数据，但现在客户想要它作为XML。 上传到他们的系统后，我可以将其下载为XML，而下面是该文件的一部分， 
 
    <?xml version="1.0" encoding="ISO-8859-1"?>
<PricatHead xmlns="xxxxx"
           preOrderTo="2015-06-01T23:59:59"
           name="15005-3"
           merchant="xxxx"
           customer="xxxxx"
           validFrom="2014-08-01T00:00:00"
           validTo="2015-06-01T23:59:59"
           currency="SEK"
           contractDiscount="0">
  <sprProduct itemID="86035-8"
              name="xxxx"
              description="xxxxxx"
              tax="0.00"
              eanCode=""
              eanType="EAN 13"
              color="BLACK"
              colorCode="C0100"
              size="36"
              sizeRange="EURO size"
              modelNo="86035"
              inSelection="N"
              productDiscount="0">
    <priceBracket quantity="1"
                  price="x"
                  grossPrice="xxxx"
                  validOrderDateFrom="2014-08-01T00:00:00"
                  validOrderDateTo="2015-06-01T00:00:00"
                  validShipDateFrom="2015-02-15T00:00:00"
                  validShipDateTo="2015-06-01T00:00:00"
                  type="PRE"/>
    <priceBracket quantity="1"
                  price="x"
                  grossPrice="x"
                  validOrderDateFrom="2015-02-15T00:00:00"
                  validOrderDateTo="2015-06-01T00:00:00"
                  validShipDateFrom="2015-02-15T00:00:00"
                  validShipDateTo="2015-06-01T00:00:00"
                  type=""/>
    <variantValue name="unit"
                  value="pcs"/>
    <variantValue name="recPrice_SEK"
                  value="x"/>
    <variantValue name="recCampaignPrice_SEK"
                  value="0"/>
    <variantValue name="brand"
                  value="x"/>
    <variantValue name="goodsClass"
                  value="4052"/>
    <variantValue name="recCampaignPriceCurrency"
                  value="SEK"/>
    <variantValue name="recWebPriceCurrency"
                  value="SEK"/>
    <variantValue name="unitType"
                  value="pcs"/>
    <variantValue name="x"
                  value="2603"/>
    <variantValue name="marketingInfo"
                  value="xxxxx"/>
    <variantValue name="preOrderDiscountHQ"
                  value="0"/>
    <variantValue name="grossPriceCurrency"
                  value="SEK"/>
    <variantValue name="productPicture_1"
                  value="xxx"/>
    <variantValue name="suppOrderCurrency"
                  value="SEK"/>
    <variantValue name="preOrderCurrency"
                  value="SEK"/>
    <variantValue name="productPicture_3"
                  value="xxx"/>
    <variantValue name="productPicture_2"
                  value="xxxx"/>
    <variantValue name="invoiceMethod"
                  value="net"/>
    <variantValue name="logisticUnit"
                  value="cu"/>
    <variantValue name="countryOfOrigin"
                  value="CN"/>
    <variantValue name="suppOrderDiscountHQ"
                  value="0"/>
    <variantValue name="customsTariffNo_SE"
                  value="6402999300"/>
  </sprProduct>
 
 对于excel表，我有一个SQL序列，它产生一个包含所有上述数据的大表，其中<sprProduct>是一行，其余列。 我没有问题创建<pricatHead>和<sprProduct>标签，但我失败了<priceBracket>因为它们也是select语句中的列，我有多个需要自己的<priceBracket> 。 <variantValue>会导致同样的问题。 
 
 难以解释但是一个sql行应该产生多个<priceBracket>和<variantValue> 。 
 
 到目前为止，我的代码如下（缩短）， 
 
--Pricat HEAD
select 
PrisLista as [@name]
,gtinMerchant as [@merchant]
,gtinCustomer as [@customer]

--Pricat ROWS
,(  select 
    ar.ArtNr as [@itemID]
    ,ar.ArtBeskr as [@name]
    ,ar.ArtBeskrLong as [@description]
    ,prl.vat as [@tax]
    ,prl.quantity as [sprProduct/priceBracket/@quantity] 
    .........
    from prl
    for xml path ('sprProduct'), type)


from prh 
FOR XML PATH ('PricatHead')
 
 如何获取特定行的每列以生成具有属性值的自己的标记？ 
 
 作为第二个问题，我还从客户那里获得了这个XML文件的XSD架构。 这可以用来帮助格式化从我的数据库导出的数据以匹配他们所需的XML文件吗？ 

I have some trouble exporting data from our database to XML. I'm probably in over my head as i have no earlier XML knowledge but hey, how hard can it be :)
 
We already send this data through an excel sheet but now the customer wants it as XML instead. After upload to their system i can download it as an XML instead and below is a part of that file,
 
    <?xml version="1.0" encoding="ISO-8859-1"?>
<PricatHead xmlns="xxxxx"
           preOrderTo="2015-06-01T23:59:59"
           name="15005-3"
           merchant="xxxx"
           customer="xxxxx"
           validFrom="2014-08-01T00:00:00"
           validTo="2015-06-01T23:59:59"
           currency="SEK"
           contractDiscount="0">
  <sprProduct itemID="86035-8"
              name="xxxx"
              description="xxxxxx"
              tax="0.00"
              eanCode=""
              eanType="EAN 13"
              color="BLACK"
              colorCode="C0100"
              size="36"
              sizeRange="EURO size"
              modelNo="86035"
              inSelection="N"
              productDiscount="0">
    <priceBracket quantity="1"
                  price="x"
                  grossPrice="xxxx"
                  validOrderDateFrom="2014-08-01T00:00:00"
                  validOrderDateTo="2015-06-01T00:00:00"
                  validShipDateFrom="2015-02-15T00:00:00"
                  validShipDateTo="2015-06-01T00:00:00"
                  type="PRE"/>
    <priceBracket quantity="1"
                  price="x"
                  grossPrice="x"
                  validOrderDateFrom="2015-02-15T00:00:00"
                  validOrderDateTo="2015-06-01T00:00:00"
                  validShipDateFrom="2015-02-15T00:00:00"
                  validShipDateTo="2015-06-01T00:00:00"
                  type=""/>
    <variantValue name="unit"
                  value="pcs"/>
    <variantValue name="recPrice_SEK"
                  value="x"/>
    <variantValue name="recCampaignPrice_SEK"
                  value="0"/>
    <variantValue name="brand"
                  value="x"/>
    <variantValue name="goodsClass"
                  value="4052"/>
    <variantValue name="recCampaignPriceCurrency"
                  value="SEK"/>
    <variantValue name="recWebPriceCurrency"
                  value="SEK"/>
    <variantValue name="unitType"
                  value="pcs"/>
    <variantValue name="x"
                  value="2603"/>
    <variantValue name="marketingInfo"
                  value="xxxxx"/>
    <variantValue name="preOrderDiscountHQ"
                  value="0"/>
    <variantValue name="grossPriceCurrency"
                  value="SEK"/>
    <variantValue name="productPicture_1"
                  value="xxx"/>
    <variantValue name="suppOrderCurrency"
                  value="SEK"/>
    <variantValue name="preOrderCurrency"
                  value="SEK"/>
    <variantValue name="productPicture_3"
                  value="xxx"/>
    <variantValue name="productPicture_2"
                  value="xxxx"/>
    <variantValue name="invoiceMethod"
                  value="net"/>
    <variantValue name="logisticUnit"
                  value="cu"/>
    <variantValue name="countryOfOrigin"
                  value="CN"/>
    <variantValue name="suppOrderDiscountHQ"
                  value="0"/>
    <variantValue name="customsTariffNo_SE"
                  value="6402999300"/>
  </sprProduct>
 
For the excel sheet i have an SQL proceedure that produces one big table with all the above data where <sprProduct> is one row and the rest columns. I have no problem to create the <pricatHead> and the <sprProduct> tags but i fail with the <priceBracket> as they are also columns in the select statement and i have multiple columns that need their own <priceBracket>. <variantValue> causes the same problem.
 
Difficult to explain but one sql row should produce multiple <priceBracket> and <variantValue>.
 
So far my code looks as below (shortened),
 
--Pricat HEAD
select 
PrisLista as [@name]
,gtinMerchant as [@merchant]
,gtinCustomer as [@customer]

--Pricat ROWS
,(  select 
    ar.ArtNr as [@itemID]
    ,ar.ArtBeskr as [@name]
    ,ar.ArtBeskrLong as [@description]
    ,prl.vat as [@tax]
    ,prl.quantity as [sprProduct/priceBracket/@quantity] 
    .........
    from prl
    for xml path ('sprProduct'), type)


from prh 
FOR XML PATH ('PricatHead')
 
How do i get each column for a specific row to produce its own tag with attributed values?
 
As a second question i also got an XSD schema for this XML file from the customer. Can this be used to help format the data exported from my database to match their required XML file?

原文：https://stackoverflow.com/questions/29274463