I can't figure out how to do this query.

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

General SQL Server Forums

New to SQL Server Programming

I can't figure out how to do this query.

Author

Topic

Booster77
Starting Member

3 Posts

Posted - 2013-02-25 : 08:55:49

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 09:07:27

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>

Booster77
Starting Member

3 Posts

Posted - 2013-02-25 : 09:25:37

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!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 10:19:44

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;

Booster77
Starting Member

3 Posts

Posted - 2013-02-26 : 03:21:58

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!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-26 : 13:41:49

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources