June 21, 2018 at 9:40 am
Greetings,
We have a vendor that is requiring that the XML we send them has name value pairs at the deepest level of the xml hierarchy.
In order to try to accommodate this I select the columns they wanted into a separate table then UnPivoted that table. All in an effort to get the FOR XML AUTO to generate the required xml.
Trying to keep it "understandable" so that future changes will be easily done by simply changing which columns are selected into the UnPivot table.
Here is the section of the xml showing the vendor 's requirement:
<OtherFields>
<Key name="Instructions1"><Value>Please write your account number on your check!</Value></Key>
<Key name="AgeTitle1"><Value>CURRENT </Value></Key>
<Key name="InvoiceBalanceFooter"><Value>-587.35</Value></Key>
</OtherFields>
The UnPivoted table: CREATE TABLE xdMOBLateNoticeRunOtherFieldsUPV
( MOBID char(20) NOT NULL
, InstallationID char(20) NOT NULL
, NoticeID char(20) NOT NULL
, name varchar(128) NOT NULL
, value varchar(2000) NULL,
CONSTRAINT [pkMOBLNROUPV] PRIMARY KEY CLUSTERED ([MOBID],[InstallationID],[NoticeID],[name] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ;
Here is the current code I have for that level of the xml hierarchy:
SELECT TOP 20 .name, RTRIM(.value) AS value
FROM xdMOBLateNoticeRunOtherFieldsUPV
FOR XML AUTO, ROOT('OtherFields'), TYPE
here is output:
<OtherFields>
<key name="OFcAddress_1" value="1621 NE Winter" />
<key name="OFcAddress_2" value="" />
<key name="OFcCity" value="CityName" />
<key name="OFcComment_id" value="" />
<key name="OFcLate_start" value="30" />
<key name="OFcName" value="Doe, Jane" />
</OtherFields>
As you see, I get attributes.
My question again: How can I make the FOR XML generate name value pairs like the vendor is requesting?
If I have to process the resulting XML through an XSLT can someone point in the right direction? (I have little experience with XSL).
thanks!
June 21, 2018 at 10:24 am
Try the following:
SELECT TOP 20 [key].name AS [@name], RTRIM([key].value) AS value
FROM xdMOBLateNoticeRunOtherFieldsUPV [key]
FOR XML AUTO, ELEMENTS, ROOT('OtherFields'), TYPE
I don't currently have a SQL instance to work with, so the syntax may be a bit off.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 21, 2018 at 11:22 am
Adding the ELEMENTS parameter changes it to the standard XML elements notation, not the Name/value pair<OtherFields>
<key>
<name>OFcAddress_1</name>
<value>1621 NE Winter</value>
</key>
<key>
<name>OFcAddress_2</name>
<value></value>
</key>
<key>
<name>OFcCity</name>
<value>City</value>
</key>
</OtherFields>
June 21, 2018 at 11:33 am
Torin - Thursday, June 21, 2018 11:22 AMAdding the ELEMENTS parameter changes it to the standard XML elements notation, not the Name/value pair<OtherFields>
<key>
<name>OFcAddress_1</name>
<value>1621 NE Winter</value>
</key>
<key>
<name>OFcAddress_2</name>
<value></value>
</key>
<key>
<name>OFcCity</name>
<value>City</value>
</key>
</OtherFields>
Yes, but aliasing the name field as [@name] should override that and still produce an attribute. It appears that you missed that change to the code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 21, 2018 at 12:43 pm
Hi Drew,
you are correct that I missed that change in the code.
Here are the results with AS [@name]
<OtherFields>
<key>
<_x0040_name>OFcAddress_1</_x0040_name>
<value>1621 NE Winter</value>
</key>
<key>
<_x0040_name>OFcAddress_2</_x0040_name>
<value></value>
</key>
Is the use of the @ special for the FOR XML?
does @name need to be declared?
thanks!!
June 21, 2018 at 1:47 pm
The following code should get you the desired resultsSELECT TOP 20 .name AS [@name], RTRIM(.value) AS [Value]
FROM xdMOBLateNoticeRunOtherFieldsUPV
FOR XML PATH('Key'), ROOT('OtherFields')
The @ indicates to the XML Parser to create an attribute instead of an element.
June 21, 2018 at 1:49 pm
Torin - Thursday, June 21, 2018 12:43 PMHi Drew,
you are correct that I missed that change in the code.
Here are the results with AS [@name]
<OtherFields>
<key>
<_x0040_name>OFcAddress_1</_x0040_name>
<value>1621 NE Winter</value>
</key>
<key>
<_x0040_name>OFcAddress_2</_x0040_name>
<value></value>
</key>Is the use of the @ special for the FOR XML?
does @name need to be declared?
thanks!!
I think it may have something to do with using AUTO instead of PATH. I almost never use AUTO. As I mentioned previously, I don't have a SQL instance to work with right now, so I can't test anything.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 21, 2018 at 2:07 pm
Excellent! You were correct, the PATH parameter caused FOR XML to interpret the @ sign to indicate an attribute.
I had to add a descriptor to instruct PATH to use "key" instead of the default "row"
SELECT TOP 20 .name AS [@name], RTRIM(.value) AS value -- the OtherFields level
FROM xdMOBLateNoticeRunOtherFieldsUPV
FOR XML PATH ('Key'), ROOT('OtherFields'), TYPE
Thank you very much!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply