[Webinar] Streamline your web hosting managementRegister Today

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

XML Select

I need to do (one of my first) select as XML statements

I need <?xml version="1.0" encoding="UTF-8" ?> at the top
Then the "root tag" is <Document>
Then a <Header> section with hard coded nodes
Then a <Record> section with firstname, lastname etc nodes

Example would be (selecting first and last name from r_users).
<?xml version="1.0" encoding="UTF-8" ?>
<Document>
  <XMLVersion Version="2.00"/>
  <Header>
       <PacketNum>0000000111222555</PacketNum>
        blah...blah..blah...
  </Header>
  <Record>
     <SeqNum>1</SeqNum>
     <firstName>Bill</firstName>
     <lastName>Gates</lastName>
         blah..blah..blah...
  </Record>
</Document>
0
lrbrister
Asked:
lrbrister
1 Solution
 
derekkrommCommented:
declare @t table (
      id int,
      fname varchar(100)
)

insert into @t select 1,'gates' union select 2,'jones'

declare @xml xml
select @xml = '<xml version="1.0" encoding="UTF-8" />
<Document>
  <XMLVersion Version="2.00"/>
  <Header>
       <PacketNum>0000000111222555</PacketNum>
        blah...blah..blah...
  </Header>'
+ (
select
      id, fname
from
      @t
for xml path('Record')
) + '
</Document>'

select @xml
0
 
lrbristerAuthor Commented:
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now