How to encapsulate table columns into individual tags?
-
My department has a tax form which has a new requirement this year that requires the data to be transmitted in XML format. In prior years it was submitted in a delimited flat-file format, so I’m helping our accountants to translate their flat SQL Server tables into the agency’s specified XML format.
I was able to translate several of the source tables into the agency's specified format without issue by using the FOR XML PATH command. However, the agency’s specs for one particular section have me stumped and I’m hoping someone out there has an idea how I can approach this.
In short, they want each source-table row encapsulated by an <itemGroup> tag, which is easy enough to accomplish. However, they also want each column encapsulated in its own <reportItem> tag. Within the <reportItem> tag they want an <rs_id> tag to store the column’s name and they want an <itemValue> tag to store the column’s data value. Further, the first column, and only the first column in the <itemGroup> must have a key=”yes” attribute. Each <rs_id> has an attribute = “mdrm”.
These two rows of dummy data are similar to the source table:
[font="Courier New"]ID SecID CountryID Currency
1 S42DWER 111344 USD
2 2TG24GG 251464 EUR [/font]
Per the agency’s specification document,
The <itemGroup ref=”xxx”> tag encapsulates a logical grouping of related items. An item group can be thought of as a single row in a database table. The <reportItem>s within the <itemGroup ref=”xxx”> tag can be thought of as the columns in that database row. The “ref” attribute provides additional context as to what the item group represents.
For example, <itemGroup ref=”Schedule 2”> indicates that the <itemGroup> and its subsequent <reportItem> tags are all related to Schedule 2. The unique identifier, or the
database key, for Schedule 2 is specified in the <reportItem key=”yes”> tag.
The <reportItem> tag encapsulates a report item.
The <reportItem key="yes"> tag is only used with the <itemGroup ref=”xxx”> tag and is always the first tag within the <itemGroup ref=”xxx”> tag. The “key=”yes”” attribute indicates that the value for this <reportItem> is the key for each of the <reportItem>s in the item group.
The <itemValue> tag reported data value for a <reportItem>
The <rs_id type=”mdrm”> is an attribute that is used internally by the FRB. The tag is a constant that does not change. Its value is the line identifier for the report item
This is example XML output taken from the agency’s specification document.
<itemGroup ref="Schedule2"><reportItem key="yes">
<rs_id type="mdrm">ID</rs_id>
<itemValue>1</itemValue>
</reportItem>
<reportItem>
<rs_id type="mdrm">SecID</rs_id>
<itemValue>S42DWER</itemValue>
</reportItem>
<reportItem>
<rs_id type="mdrm">CountryID</rs_id>
<itemValue>111344</itemValue>
</reportItem>
<reportItem>
<rs_id type="mdrm">Currency</rs_id>
<itemValue>USD</itemValue>
</reportItem>
<reportItem>
</itemGroup>
<itemGroup ref="Schedule2">
<reportItem key="yes">
<rs_id type="mdrm">ID</rs_id>
<itemValue>2</itemValue>
</reportItem>
<reportItem>
<rs_id type="mdrm">SecID</rs_id>
<itemValue>2TG24GG</itemValue>
</reportItem>
<reportItem>
<rs_id type="mdrm">CountryID</rs_id>
<itemValue>251464</itemValue>
</reportItem>
<reportItem>
<rs_id type="mdrm">Currency</rs_id>
<itemValue>EUR</itemValue>
</reportItem>
<reportItem>
</itemGroup>
-
See if this helps
DECLARE @t TABLE(ID INT,SecID VARCHAR(10),CountryID VARCHAR(10),Currency VARCHAR(3))
INSERT INTO @t(ID,SecID,CountryID,Currency)
SELECT 1,'S42DWER','111344','USD' UNION ALL
SELECT 2,'2TG24GG','251464','EUR';
SELECT 'Schedule2' AS "@ref",
(SELECT 'yes' AS "@key",
(SELECT 'mdrm' AS "@type",
'ID' AS "text()"
FOR XML PATH('rs_id'),TYPE),
ID AS "itemValue"
FOR XML PATH('reportItem'),TYPE),
(SELECT
(SELECT 'mdrm' AS "@type",
'SecID' AS "text()"
FOR XML PATH('rs_id'),TYPE),
SecID AS "itemValue"
FOR XML PATH('reportItem'),TYPE),
(SELECT
(SELECT 'mdrm' AS "@type",
'CountryID' AS "text()"
FOR XML PATH('rs_id'),TYPE),
CountryID AS "itemValue"
FOR XML PATH('reportItem'),TYPE),
(SELECT
(SELECT 'mdrm' AS "@type",
'Currency' AS "text()"
FOR XML PATH('rs_id'),TYPE),
Currency AS "itemValue"
FOR XML PATH('reportItem'),TYPE)
FROM @t
FOR XML PATH('itemGroup');
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark,
Thank you for helping with that so quickly, your code worked perfectly. I was trying to make the task much more difficult than it needed to be. Somehow I got it into my head that I needed to programmatically pull the column names into the code, but your solution showed me how unnecessary that would have been.
-
You re welcome, glad it helped.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
rybo (2/1/2015)
Per the agency’s specification document,Can you, by any chance, provide a link to that spec? I'd like to cite it for something else I'm doing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (2/1/2015)
rybo (2/1/2015)
Per the agency’s specification document,Can you, by any chance, provide a link to that spec? I'd like to cite it for something else I'm doing.
Sure, it can be found on this page: https://www.frbservices.org/centralbank/reportingcentral/reportingcentral_userguides.html
Here is the direct link to this document:
https://www.frbservices.org/files/reporting/pdf/benchmark_series_xml_file_format.pdf
-
rybo (2/2/2015)
Jeff Moden (2/1/2015)
rybo (2/1/2015)
Per the agency’s specification document,Can you, by any chance, provide a link to that spec? I'd like to cite it for something else I'm doing.
Sure, it can be found on this page: https://www.frbservices.org/centralbank/reportingcentral/reportingcentral_userguides.html
Here is the direct link to this document:
https://www.frbservices.org/files/reporting/pdf/benchmark_series_xml_file_format.pdf
Outstanding! You've just made my day!
Shifting gears a bit and with no reflection on you (I'm just venting and want you to be sure that you know I'm not throwing rocks at you :-)), it's just absolutely amazing to me that they decided to convert a rather simple CSV where two rows take 40 characters and change it to one of the worst tag bloated XML monsters of 1560 characters (2 bytes per character in XML) for the same thing. It's no wonder that people complain about performance on such things.
It also is an indication why people have to worry about "big data" when they blow out such simple data by a factor of 38 times (that's 3,800%!!! bigger). What a sickening waste of bandwidth, storage, and cpu time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply