Creating an XML document within SQL - Attributes

Chris · April 19, 2016, 3:52pm

Hello. I have been tasked with creating an XML document for my company.

I have used XML in the past for EDI but I have never done XML with attributes. I have googled the past day and have not found many solutions so I figured I would post here.
I was able to figure out how to add the attributes via @vaiable name but I am unable to control the Child/Parent relationship. Here is what I have so far:

select
(select top 1 'TST1' AS [@CompanyID],
'83' AS [@PmtRecCount],
'81400.00' AS [@PmtRecTotal],
'http://www.w3.org/2001/XMLSchema-instance' AS [@xsi]
for xml PATH('File'),
TYPE),
(select top 1 'CHK' AS [@PmtMethod],
'C' AS [@PmtCrDr]
for xml PATH('PmtRec'),
TYPE)

for xml PATH('')
Example of the output: (including the header info)
"

<?xml version="1.0"?> 7 This is the Check Memo field #### ###### Courier Name here >######</ >######</ "

JamesK · April 19, 2016, 4:04pm

Unfortunately, the crappy formatter messes up the XML result you posted, so I don't know what you are looking for. Following is an example which you can copy and paste to your SSMS window and run to see what it produces. Perhaps you can pattern your query after this or make appropriate tweaks?

CREATE TABLE #tmp(col1 INT, col2 VARCHAR(32), col3 FLOAT, 
    col4 VARCHAR(32), col5 VARCHAR(32));
INSERT INTO #tmp VALUES (1,'A',2.7,'x','y'),(2,'B',8.8,'u','v');

    SELECT
    	col2 as [@col2],
    	col1,
    	col3,
    	(
    		SELECT
    			col4,
    			col5
    		FROM
    			#tmp b
    		WHERE
    			b.col3 = a.col3
    		FOR 
    			XML PATH('SubNode'),TYPE
    	) 
    FROM
    	#tmp a
    FOR XML PATH('NodeName'), ROOT('rootname');

    DROP TABLE #tmp;

Chris · April 19, 2016, 5:30pm

Thanks for the reply. Ahh, you are right. The XML got butchered.

Basically, I have a total of around 40 "header" tags if you want to call them that. Some have attributes or child tags .

Do you think that would be a good format to use for so many tags?

Chris · April 19, 2016, 5:50pm

Based on my research, I was thinking FOR XML EXPLICIT was the way to go but I have never seen attributes within another tag. Is this possible?

JamesK · April 19, 2016, 6:04pm

I did not understand what you meant by "attributes within tag". The code you posted generates the output shown in the screenshot when I run it. (I added a root element to better visualize the hierarchy) Can you post a screenshot of what you are looking for if this is not it?

<root>
  <File CompanyID="TST1" PmtRecCount="83" PmtRecTotal="81400.00" xsi="http://www.w3.org/2001/XMLSchema-instance" />
  <PmtRec PmtMethod="CHK" PmtCrDr="C" />
</root>

While XML EXPLICIT perhaps has more features, it is harder to program and maintain (at least in my experience). So almost always, I end up using XML PATH.

Chris · April 19, 2016, 6:18pm

The picture above nearly gets the job done but I have a feeling the SQL code will get very messy. Also, the PmtRec line is not indented to signify a child tag to File.

<img src="/uploads/db8100/original/1X/3232cbb002f0c8576333de07ea88b43c223aa589.png" width="549" height="500">

When I say attributes, I am referring to the PmtRec line. It has PmtMethod and PmtCrDr. its all within PMT Rec.
The child will be RefInfo...Message...Check. That kind of make sense?

This is about 1/8 of the XML I will need to map. I am trying to figure out the best way to go about and get this done.

We have 3 ways we will be sending the information to another source. 3 options..check/card/ahc. I will have to add logic to differentiate but I will worry about that later.

Thanks for all your help

JamesK · April 19, 2016, 6:41pm

If you were trying to upload an image, I don't see it. Also, you said the "The child will be RefInfo...Message...Check.". I don't see a RefInfo anywhere in the code or text you posted previously.

Is this what you are looking for?

<root>
  <File CompanyID="TST1" PmtRecCount="83" PmtRecTotal="81400.00" xsi="http://www.w3.org/2001/XMLSchema-instance">
    <PmtRec PmtMethod="CHK" PmtCrDr="C" />
  </File>
</root>

That is generated from this code

SELECT  ( SELECT TOP 1
                    'TST1' AS [@CompanyID] ,
                    '83' AS [@PmtRecCount] ,
                    '81400.00' AS [@PmtRecTotal] ,
                    'http://www.w3.org/2001/XMLSchema-instance' AS [@xsi],
					( SELECT TOP 1
								'CHK' AS [@PmtMethod] ,
								'C' AS [@PmtCrDr]
					FOR
					  XML PATH('PmtRec') ,
						  TYPE
					)
        FOR
          XML PATH('File') ,
              TYPE
        )
FOR     XML PATH('root');

Chris · April 19, 2016, 6:53pm

Sorry about that. I am new to website. I tried uploading the pic again. Lets see if that works again.

Your above example is exactly what I need to do but eventually all the hardcoded information will be pulled from a view we will create. So basically I just need to keep nesting the XML path? This will be one messy query. Do you think this is the best way to go about it?

n img src.

JamesK · April 19, 2016, 7:16pm

The XML you posted does not seem very complex. You have to construct the nested pieces and then nest them within the outer XML. Since I don't have the schema and data for your tables, I don't know what your query would look like, but here is an example where multi-level nesting is done. This is not the only way to do it, I am just showing couple of different ways. You can copy this code and run it from an SSMS window.

You can see that depending on the relationships, sometimes there are more nested nodes, sometimes fewer etc.

You might find Jacob Sebastian's blog useful.

CREATE TABLE #tmp(col1 INT, col2 VARCHAR(32), col3 VARCHAR(32), 
    col4 VARCHAR(32), col5 VARCHAR(32));
INSERT INTO #tmp VALUES (1,'A','11111','x','y'),(2,'B','222222','u','v');
INSERT INTO #tmp VALUES (3,'R','ZZZZZ','xx','yy'),(4,'S','222222','uu','vv');


SELECT
	col1,
	col2 AS [sub1/@col2] ,
	sub1
FROM
	#tmp a
	CROSS APPLY
	(
		SELECT
			col3 ,
			sub2
		FROM
			#tmp b
			CROSS APPLY
			(
				SELECT col4, a.col5
				FROM
					#tmp c
				WHERE
					c.col3 = b.col3
				FOR XML PATH(''), TYPE
			) c(sub2)
				
				
		WHERE
			a.col1 = b.col1
		FOR XML PATH(''),TYPE
	)b(sub1)
			
FOR XML PATH(''), ROOT('root');

DROP TABLE #tmp;

Chris · April 19, 2016, 7:24pm

You have beyond more than helpful.
I believe that most recent post is the way to go.

I think my first step needs to be creating the view so that I have data to work with instead of using random hardcode data. It will make more sense for me. I will just need to insert the data into the temp table and then call the data back into the nested selects.

Thanks again JamesK for sticking around today. Ill be back if I have any more questions.