SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 I can't figure out how to do this query.		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Booster77
Starting Member

Netherlands
3 Posts
Posted - 02/25/2013 :  08:55:49  Show Profile  Reply with Quote
Hello everyone,

First of all thank you for reading this and hopefully for the help. I've been stuck for ages now on a problem. I will try to explain as clear as possible.

I have thousands of rows of data (debtors and invoices information) in a table. With that data I need to build an XML. One debtor can (and does) have multiple invoices in the table, so the same debtornumber appears on multiple rows.

So a row would look like (I've simplified it, no need to flush this post):
[ID]----[DEBTORNUMBER]-----[INVOICENUMBER]----[VALUE]------[DEBTORVALUE]
1----------12345--------------1000----------------999------------15000
2----------12345--------------1001----------------593------------15000
3----------12345--------------1002----------------19-------------15000
4----------98023--------------500-----------------223------------8000

In this XML I need one header for each debtor, and then multiple 'lines' for each invoice that belongs to that debtor.

What I actually need is something I will literally write down, so you hopefully can convert it into a query for me;

For each unique debtornumber (which in above example would be 12345 and 98023
Return debtornumber, debtorvalue once (because here I write my header)
Return invoicenumber, value as many times as they appear for the debtornumber (because these will be written in 'lines'


I hope that makes any sense to you, because it's really hard to explain.

So I only need the header info (debtornumber and debtorvalue in my example) once... and the rest of the information as many times as there are invoices.

Is what I want possible in a single query? And if not how can I solve this best?

Thank you so much in advance.

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/25/2013 :  09:07:27  Show Profile  Reply with Quote
If you can post the XML that you want to see that corresponds to the sample data you posted, that would be more helpful than describing the XML in words. For example (and this is not necessarily correct), like this:
<Debts>
  <DebtorNumber ID="12345">
    <DebtItem InvoiceNumber="1000" Value="999" />
    <DebtItem InvoiceNumber="1001" Value="593" />
    <DebtItem InvoiceNumber="1002" Value="19" />
  </DebtorNumber>
  <DebtorNumber ID="98023">
    <DebtItem InvoiceNumber="500" Value="223" />
  </DebtorNumber>
</Debts>
Go to Top of Page

Booster77
Starting Member

Netherlands
3 Posts
Posted - 02/25/2013 :  09:25:37  Show Profile  Reply with Quote
Thank you for your reply James. You're probably right. Based on my example above, the XML would look like:

<summary>
  <dimension>12345</dimension>
  <debtorvalue>24253.10</debtorvalue>
      <details>
        <detail>
          <id>1</id> 
          <invoicenumber>1000</invoicenumber>
          <value>999</value>
        </detail>
        <detail>
          <id>2</id> 
          <invoicenumber>1001</invoicenumber>
          <value>593</value>
        </detail>
        <detail>
          <id>3</id> 
          <invoicenumber>1002</invoicenumber>
          <value>19</value>
        </detail>
      </details>
</summary>
<summary>
 <dimension>98023</dimension>
  <debtorvalue>8000</debtorvalue>
      <details>
        <detail>
          <id>1</id> 
          <invoicenumber>500</invoicenumber>
          <value>223</value>
        </detail>
     </details>
</summary>


With dimension and debtorvalue being the headervalues I was talking about and the lines in detail are the actual invoice lines. Each debtor can only have one header in the entire XML and that's where I'm struggling.

Thank you!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/25/2013 :  10:19:44  Show Profile  Reply with Quote
See if this works?
CREATE TABLE #tmp (id INT, DebtorNumber INT, InvoiceNumber INT, VALUE INT, DebtorValue INT);
INSERT INTO #tmp VALUES
(1,12345,1000,999,15000),
(2,12345,1001,593,15000),
(3,12345,1002,19,15000),
(4,98023,500,223,8000)

SELECT
	debtornumber AS dimension,
	debtorvalue,
	(
		SELECT a.id,a.invoicenumber, a.value
		FROM #tmp a
		WHERE a.DebtorNumber = b.debtornumber
		FOR XML PATH('detail'),TYPE
	) AS details
FROM
	#tmp b
FOR XML PATH(''), ROOT('summary');

DROP TABLE #tmp;
Go to Top of Page

Booster77
Starting Member

Netherlands
3 Posts
Posted - 02/26/2013 :  03:21:58  Show Profile  Reply with Quote
quote:
Originally posted by James K

See if this works? ...


That works just awesome James! Thank you very much! I have never known this was even possible with SQL!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/26/2013 :  13:41:49  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000