Adding an attribute to the xml root using FOR XML PATH syntax

I would like to include an additional attribute at the root level of the xml that is being produced by the statement below.

So that:

<FrameworkBatch xmlns:noNamespaceSchemaLocation="http://gtvpmapp02/GoScript/Schema/FrameworkBatch.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

would then become:

<FrameworkBatch xmlns:noNamespaceSchemaLocation="http://gtvpmapp02/GoScript/Schema/FrameworkBatch.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" GoScriptName="test">
with xmlnamespaces(
	'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
,	'http://gtvpmapp02/GoScript/Schema/FrameworkBatch.xsd' as "noNamespaceSchemaLocation"
)
select isnull(command,'') 'Command', 
isnull(DataType,'') 'DataType', 
isnull(RemoteBaseDirectory,'') 'RemoteBaseDirectory', 
isnull(RemoteFile, '') 'RemoteFile', 
isnull(LocalFile,'') 'LocalFile', 
isnull(LastRemoteFile,'') 'LastRemoteFile',
isnull(LastLocalFile,'') 'LastLocalFile' ,
isnull(LastAttempt,'') 'LastAttempt' ,
isnull(LastSuccess,'') 'LastSuccess' ,
isnull(LastStatus,'') 'LastStatus' 
from gtdev02.industryexposure.dbo.FTPBatch 
where batchfilename = 'VinFTPBatch1.xml'
for xml path('FTP'), Root('FrameworkBatch'), ELEMENTS XSINIL

Open in new window

DoubleV47Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
See the Microsoft reference: http://msdn.microsoft.com/en-us/library/ms345137.aspx

From my understanding, you would just make this a literal column like this:
'test' AS "@GoScriptName"

So the query would be updated as follows:
with xmlnamespaces(
	'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
,	'http://gtvpmapp02/GoScript/Schema/FrameworkBatch.xsd' as "noNamespaceSchemaLocation"
)
select 'test' AS "@GoScriptName",
isnull(command,'') 'Command', 
isnull(DataType,'') 'DataType', 
isnull(RemoteBaseDirectory,'') 'RemoteBaseDirectory', 
isnull(RemoteFile, '') 'RemoteFile', 
isnull(LocalFile,'') 'LocalFile', 
isnull(LastRemoteFile,'') 'LastRemoteFile',
isnull(LastLocalFile,'') 'LastLocalFile' ,
isnull(LastAttempt,'') 'LastAttempt' ,
isnull(LastSuccess,'') 'LastSuccess' ,
isnull(LastStatus,'') 'LastStatus' 
from gtdev02.industryexposure.dbo.FTPBatch 
where batchfilename = 'VinFTPBatch1.xml'
for xml path('FTP'), Root('FrameworkBatch'), ELEMENTS XSINIL

Open in new window

0
DoubleV47Author Commented:
That's almost it.  It puts the GoScriptName attribute on the <FTP> node, not the <FrameworkBatch> node.
0
Kevin CrossChief Technology OfficerCommented:
You can path it like this:
with xmlnamespaces(
	'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
,	'http://gtvpmapp02/GoScript/Schema/FrameworkBatch.xsd' as "noNamespaceSchemaLocation"
)
select 'test' AS "FrameworkBatch/@GoScriptName",
isnull(command,'') 'Command', 
isnull(DataType,'') 'DataType', 
isnull(RemoteBaseDirectory,'') 'RemoteBaseDirectory', 
isnull(RemoteFile, '') 'RemoteFile', 
isnull(LocalFile,'') 'LocalFile', 
isnull(LastRemoteFile,'') 'LastRemoteFile',
isnull(LastLocalFile,'') 'LastLocalFile' ,
isnull(LastAttempt,'') 'LastAttempt' ,
isnull(LastSuccess,'') 'LastSuccess' ,
isnull(LastStatus,'') 'LastStatus' 
from gtdev02.industryexposure.dbo.FTPBatch 
where batchfilename = 'VinFTPBatch1.xml'
for xml path('FTP'), Root('FrameworkBatch'), ELEMENTS XSINIL

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

DoubleV47Author Commented:
Nope, that puts <FrameworkBatch GoScriptName="test"/> as the first node under the <FTP> node
0
Kevin CrossChief Technology OfficerCommented:
Arg!  Sorry, not sure why that is not working correctly.  Think maybe that method works for the root node.  Let me keep thinking on this -- I will post back.
0
Mark WillsTopic AdvisorCommented:
The XSINIL is telling the XML output to create a namespace section / header at the top.

Do not think you can intercept the way you are asking. Have done some like this, but not having both the XMLNAMESPACES and XSINIL at the same time as wanting to get an element...

Have not given up, always enjoy a good challenge, but so far, the automated way it simply not cutting it...

for example, to get the header you are looking for (nearly), I would use :

with xmlnamespaces(
      'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
,      'http://gtvpmapp02/GoScript/Schema/FrameworkBatch.xsd' as "noNamespaceSchemaLocation"
)
select
(
select 'TEST' as 'GoScriptName' for xml raw('FrameworkBatch'),type
)

Now to get the rest to be part of the same path of 'FrameworkBatch' is proving the challenge - normally just add another select, but with the "with xmlnamespaces" it is trying to ecapsulate an entire new node.

Might have to go to a manually produced result (as it code for it - still within SQL).

Will get back - just thought I need to share the fact that I have spent a bit of time on this problem, and haven't given up yet...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DoubleV47Author Commented:
I appreciate your efforts!
0
Mark WillsTopic AdvisorCommented:
@mwvisa1:  my email is on my bio - would like to say g'day if you have the time...
0
DoubleV47Author Commented:
Thanks for all of your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.