XML with attributes - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

XML with attributes

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-01-17 : 10:40:34

I have the following query that generates element centric xml:

SELECT TOP 100 
		(SELECT *
		 FROM	dbo.CRDM_Header HD WITH (NOLOCK)
		 WHERE	HD.TransactionID = H.TransactionID
		 FOR XML PATH('Header'), TYPE)
		,
		 (SELECT *
		  FROM dbo.CRDM_RecalledTransaction RT WITH (NOLOCK)
		  WHERE RT.TransactionID = H.TransactionID
		  FOR XML PATH('RecalledTransaction'), ROOT('RecalledTransactionRecords'), TYPE)
		  ,
		  (SELECT *
		  FROM dbo.CRDM_Receipt R WITH (NOLOCK)
		  WHERE R.TransactionID = H.TransactionID
		  FOR XML PATH('Receipt'), ROOT('ReceiptRecords'), TYPE)
		  ,
		  (SELECT *
		  FROM dbo.CRDM_StaffDiscount SD WITH (NOLOCK)
		  WHERE SD.TransactionID = H.TransactionID
		  FOR XML PATH('StaffDiscount'), ROOT('StaffDiscountRecords'), TYPE)
		  ,
		  (SELECT *
		  FROM dbo.CRDM_Tender T WITH (NOLOCK)
		  WHERE T.TransactionID = H.TransactionID
		  FOR XML PATH('Tender'), ROOT('TenderRecords'), TYPE)	
FROM dbo.CRDM_Header H  WITH (NOLOCK)
FOR XML PATH('CRDMPointOfSaleTransaction'), TYPE, ROOT('CRDMPointOfSaleTransactions')

However, I want the subqueries to generate attribute centric xml, yet without having to specify the columns explicitly.

So rather than:

<CRDMPointOfSaleTransactions>
  <CRDMPointOfSaleTransaction>
    <Header>
      <TransactionID>2010072901101040679</TransactionID>
      <InsertedDateTime>2010-07-29T00:13:20.177</InsertedDateTime>
      <EndTransDateTime>2010-07-29T00:13:13</EndTransDateTime>
    </Header>
    <FastFact>
      <TransactionID>2010072901101040679</TransactionID>
      <InsertedDateTime>2010-07-29T00:13:20.177</InsertedDateTime>
      <UpdatedDateTime>2010-07-29T00:13:20.177</UpdatedDateTime>
    </FastFact>
    <ItemRecords>
      <Item>
        <TransactionID>2010072901101040679</TransactionID>
        <RecordSeqNo>12762</RecordSeqNo>
        <InsertedDateTime>2010-07-29T00:13:20.177</InsertedDateTime>
        <EndTransDateTime>2010-07-29T00:13:13</EndTransDateTime>
      </Item>
      <Item>
        <TransactionID>2010072901101040679</TransactionID>
        <RecordSeqNo>12763</RecordSeqNo>
        <InsertedDateTime>2010-07-29T00:13:20.177</InsertedDateTime>
        <EndTransDateTime>2010-07-29T00:13:13</EndTransDateTime>
      </Item>
    </ItemRecords>
    <OperatorActionRecords>
      <OperatorAction>
        <TransactionID>2010072901101040679</TransactionID>
        <RecordSeqNo>12768</RecordSeqNo>
      </OperatorAction>
    </OperatorActionRecords>
    <ReceiptRecords>
      <Receipt>
        <TransactionID>2010072901101040679</TransactionID>
        <RecordSeqNo>12788</RecordSeqNo>
      </Receipt>
    </ReceiptRecords>
    <TenderRecords>
      <Tender>
        <TransactionID>2010072901101040679</TransactionID>
        <RecordSeqNo>12769</RecordSeqNo>
        <InsertedDateTime>2010-07-29T00:13:20.177</InsertedDateTime>
        <EndTransDateTime>2010-07-29T00:13:13</EndTransDateTime>
      </Tender>
    </TenderRecords>
  </CRDMPointOfSaleTransaction>
</CRDMPointOfSaleTransactions>

I have:

<CRDMPointOfSaleTransactions>
  <CRDMPointOfSaleTransaction>
    <Header  TransactionID = "2010072901101040679" InsertedDateTime = "2010-07-29T00:13:20.177" EndTransDateTime = "2010-07-29T00:13:13" />
    </Header>
    <FastFact TransactionID = "2010072901101040679" InsertedDateTime = "2010-07-29T00:13:20.177" EndTransDateTime = "2010-07-29T00:13:13" />
    <ItemRecords>
      <Item TransactionID = "2010072901101040679" InsertedDateTime = "2010-07-29T00:13:20.177" EndTransDateTime = "2010-07-29T00:13:13" />
      <Item TransactionID = "2010072901101040679" InsertedDateTime = "2010-07-29T00:13:20.177" EndTransDateTime = "2010-07-29T00:13:13" />
    </ItemRecords>
    <OperatorActionRecords>
 etc
  </CRDMPointOfSaleTransaction>
</CRDMPointOfSaleTransactions>
[code]

Hearty head pats

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-17 : 10:57:27

You can certainly do that if you are willing to use the RAW mode for the subqueries, but, as far as I know, in PATH mode you have to list the columns explicitly and alias them with the "@" qualifier if you want them to be attributes. Using RAW mode, for example, your first subquery would be:

(SELECT *
FROM	dbo.CRDM_Header HD WITH (NOLOCK)
WHERE	HD.TransactionID = H.TransactionID
FOR XML RAW('Header'), TYPE)

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-01-17 : 11:06:36

That's perfect. Thanks a lot Sunita :)

Hearty head pats

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-17 : 11:14:25

You are very welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources