Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

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

0
DoubleV47
Asked:
DoubleV47
  • 4
  • 3
  • 2
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now