Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql for xml

Posted on 2011-10-12
3
Medium Priority
?
294 Views
Last Modified: 2012-06-22
Getting error: xml document cannot contain multiple root level elements.  (I know why, just not sure how to fix it).

I'd like to generate this:
<root uid="me" pwd="123">
      <group gid="222">
            <transaction>
                  <trxid>1</trxid>
                  <trxdate>12/25/2010</trxdate>
                  <trxamt>10.00</trxamt>
            </transaction>
      </group>
      <group gid="222">
            <transaction>
                  <trxid>2</trxid>
                  <trxdate>12/31/2010</trxdate>
                  <trxamt>15.00</trxamt>
            </transaction>
      </group>
</root>

But I'm getting this:
<root uid="me" pwd="123">
  <group gid="222">
    <transaction>
      <trxid>1</trxid>
      <trxdate>12/25/2010</trxdate>
      <trxamt>10.00</trxamt>
    </transaction>
  </group>
</root>
<root uid="me" pwd="123">
  <group gid="222">
    <transaction>
      <trxid>2</trxid>
      <trxdate>12/31/2010</trxdate>
      <trxamt>15.00</trxamt>
    </transaction>
  </group>
</root>

How do I fix the query?  (see code for sample table)

SELECT 'me' AS '@uid', '123' AS '@pwd',
ISNULL(CONVERT(VARCHAR(25), transactiongroupid), '') AS 'group/@gid',
CONVERT(VARCHAR(25), transactionid) AS 'group/transaction/trxid',
CONVERT(VARCHAR(10), transactiondate, 101) AS 'group/transaction/trxdate',
ISNULL(CONVERT(VARCHAR(10), transactionamt), '') AS 'group/transaction/trxamt'
FROM tempdb.dbo.mh_group_transactions
WHERE filesentdate IS NULL
FOR XML PATH('root')
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = 'mh_group_transactions' AND type = 'U' AND UID = 1)
DROP TABLE tempdb.dbo.mh_group_transactions

CREATE TABLE tempdb.dbo.mh_group_transactions (
transactionid int not null,
transactiongroupid int null,
transactiondate smalldatetime null,
transactionamt money null,
filesentdate smalldatetime null)

INSERT INTO tempdb.dbo.mh_group_transactions VALUES (1, 222, '12/25/2010', 10.00, NULL)
INSERT INTO tempdb.dbo.mh_group_transactions VALUES (2, 222, '12/31/2010', 15.00, NULL)

Open in new window

0
Comment
Question by:MariaHalt
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36956909
Hi.

Are you sure there should be two <group gid="222"> elements or should the two transactions being under one?

Here is an example of one way to accomplish what you want. Another would be using FOR XML EXPLICIT where you have to build out the entire XML document using explicit placement and UNION operators.


/* Test data. */
DECLARE @mh_group_transactions TABLE (
   transactionid int not null,
   transactiongroupid int null,
   transactiondate smalldatetime null,
   transactionamt money null,
   filesentdate smalldatetime null
);

INSERT INTO @mh_group_transactions VALUES (1, 222, '12/25/2010', 10.00, NULL);
INSERT INTO @mh_group_transactions VALUES (2, 222, '12/31/2010', 15.00, NULL);

/*
<!-- desired output -->
<root uid="me" pwd="123">
      <group gid="222">
            <transaction>
                  <trxid>1</trxid>
                  <trxdate>12/25/2010</trxdate>
                  <trxamt>10.00</trxamt>
            </transaction>
      </group>
      <group gid="222">
            <transaction>
                  <trxid>2</trxid>
                  <trxdate>12/31/2010</trxdate>
                  <trxamt>15.00</trxamt>
            </transaction>
      </group>
</root>
*/
SELECT 'me' AS '@uid', '123' AS '@pwd'
     , (
       SELECT ISNULL(CONVERT(VARCHAR(25), transactiongroupid), '') AS "@gid"
            , (
              SELECT CONVERT(VARCHAR(25), transactionid) AS trxid
                   , CONVERT(VARCHAR(10), transactiondate, 101) AS trxdate
                   , ISNULL(CONVERT(VARCHAR(10), transactionamt), '') AS trxamt
			  FROM @mh_group_transactions t2
			  WHERE t1.transactiongroupid = t2.transactiongroupid
			  AND t2.filesentdate IS NULL
			  FOR XML PATH('transaction'), TYPE
              )
	   FROM @mh_group_transactions t1
	   WHERE filesentdate IS NULL
	   GROUP BY transactiongroupid
	   FOR XML PATH('group'), TYPE
       )
FOR XML PATH('root')
;

/*
<!-- actual output -->
<root uid="me" pwd="123">
  <group gid="222">
    <transaction>
      <trxid>1</trxid>
      <trxdate>12/25/2010</trxdate>
      <trxamt>10.00</trxamt>
    </transaction>
    <transaction>
      <trxid>2</trxid>
      <trxdate>12/31/2010</trxdate>
      <trxamt>15.00</trxamt>
    </transaction>
  </group>
</root>
*/

Open in new window

0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 36956921
If you truly want the desired output above, just make the modification shown below:
/*
<!-- desired output -->
<root uid="me" pwd="123">
      <group gid="222">
            <transaction>
                  <trxid>1</trxid>
                  <trxdate>12/25/2010</trxdate>
                  <trxamt>10.00</trxamt>
            </transaction>
      </group>
      <group gid="222">
            <transaction>
                  <trxid>2</trxid>
                  <trxdate>12/31/2010</trxdate>
                  <trxamt>15.00</trxamt>
            </transaction>
      </group>
</root>
*/
SELECT 'me' AS '@uid', '123' AS '@pwd'
     , (
       SELECT ISNULL(CONVERT(VARCHAR(25), transactiongroupid), '') AS "@gid"
            , (
              SELECT CONVERT(VARCHAR(25), transactionid) AS trxid
                   , CONVERT(VARCHAR(10), transactiondate, 101) AS trxdate
                   , ISNULL(CONVERT(VARCHAR(10), transactionamt), '') AS trxamt
			  FROM @mh_group_transactions t2
			  WHERE /*t1.transactiongroupid = t2.transactiongroupid
			  AND t2.filesentdate IS NULL*/ t1.transactionid = t2.transactionid
			  FOR XML PATH('transaction'), TYPE
              )
	   FROM @mh_group_transactions t1
	   WHERE filesentdate IS NULL
	   -- GROUP BY transactiongroupid
	   FOR XML PATH('group'), TYPE
       )
FOR XML PATH('root')

Open in new window

0
 

Author Closing Comment

by:MariaHalt
ID: 36957384
Perfect!  Thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question