I am reading 2 values from a database table, Status and SubStatus are the
column names.  I am trying to create my output as xml.


SELECT tablename.status as '@status'
             ,tablename.substatus
    FROM tablename
      FOR XML PATH


Results:

<row status="Open">
    <SubStatus>Stage 1</SubStatus>
</row>


I want it to be:

<SubStatus status="Open">Stage 1</SubStatus>

Hello Loren,

use scratch
go
create table dbo.tablename(status nchar(4),substatus nvarchar(10))
insert into dbo.tablename values ('Open','Stage 1')
go
SELECT tablename.status as '@status'
,tablename.substatus as 'text()'
FROM tablename
FOR XML PATH('SubStatus')

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Kent thanks for the sample, it creates the xml that I was looking for.

So then is it possible to take the following select and place an attribute
on the SubStatus node.


SELECT *
  FROM (
        SELECT gf.SubStatus
               ,gf.Status
               ,gf.ProjectID
               ,gf.GlobalFolderID
               ,gf.AutoNumberProjectID
               ,ROW_NUMBER() OVER(ORDER BY gf.Description ASC) AS RowNumber
          FROM GlobalFolder gf
       ) AS rows
WHERE RowNumber BETWEEN 1 AND 15
   FOR XML PATH ('Folder')
    ,TYPE

Here is the current results from the above statement:

<Folder>
  <SubStatus>Open</SubStatus>
  <Status>Stage 1</Status>
  <ProjectID>1758</ProjectID>
  <GlobalFolderID>30</GlobalFolderID>
  <AutoNumberProjectID>1758</AutoNumberProjectID>
  <RowNumber>1</RowNumber>
</Folder>
<Folder>
  <SubStatus></SubStatus>
  <Status></Status>
  <ProjectID>1725</ProjectID>
  <GlobalFolderID>14</GlobalFolderID>
  <AutoNumberProjectID>1725</AutoNumberProjectID>
  <RowNumber>2</RowNumber>
</Folder>


Looking for this:

<Folder>
  <SubStatus status="Stage 1">Open</SubStatus>
  <ProjectID>1758</ProjectID>
  <GlobalFolderID>30</GlobalFolderID>
  <AutoNumberProjectID>1758</AutoNumberProjectID>
  <RowNumber>1</RowNumber>
</Folder>
<Folder>
  <SubStatus status=""></SubStatus>
  <ProjectID>1725</ProjectID>
  <GlobalFolderID>14</GlobalFolderID>
  <AutoNumberProjectID>1725</AutoNumberProjectID>
  <RowNumber>2</RowNumber>
</Folder>


Thanks,
Loren Dummer


Show quoteHide quote

Hello Loren,

Yes, something like this. Didn't test, just off the top of my head.

SELECT    gf.status as '@status'
    , projectID
    , AutoNumberProjectID
    , RowNumber
FROM (
SELECT gf.SubStatus
,gf.Status
,gf.ProjectID
,gf.GlobalFolderID
,gf.AutoNumberProjectID
,ROW_NUMBER() OVER(ORDER BY gf.Description ASC) AS RowNumber
FROM GlobalFolder gf
) AS rows
WHERE RowNumber BETWEEN 1 AND 15
FOR XML PATH ('Folder'),ELEMENTS XSINIL,TYPE

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Kent,

I finally got it to work and then noticed your posting.  The only
differrence is I had to rename the '@status' column name to
'SubStatus/@status'.

Thanks for your help.

Loren

Show quoteHide quote

Extracting xml values using xquery
Un-encoding XML special characters, please help
dynamic xpath in XQUERY exist() function
"nodes" is not a valid function, property, or field.
Predicate value when retrieving a value