Solved

XML Select

Posted on 2011-03-04
8
472 Views
Last Modified: 2012-05-11
My attached code works exactly the way I need...but

I REALLY need the line
'<xml version="1.0" encoding="UTF-8"/>

To be this
<?xml version="1.0" encoding="UTF-8"?>

But everytime I try that it errors out
Declare @packet varchar(30)
Set @packet =	(SELECT '0000' + Cast(CAST(RAND() * 1000000000 AS INT) as varchar) + 
				Cast(CAST(RAND() * 1000000000 AS INT) as varchar))

Declare @clientAcctNum	varchar(10)
Set @clientAcctNum = '00123456'

Declare @clientUserID	varchar(10)
Set @clientUserID = '002345'

Declare @contactEmail	varchar(30)
Set @contactEmail = 'lbristerg@mydomain.com'

Declare @contactName	varchar(50)
Set @contactName = 'Bill Gatesg'

Declare @contactPhone	varchar(12)
Set @contactPhone = '(111)222-3333'

declare @xml xml
select @xml = '<xml version="1.0" encoding="UTF-8"/>
<Document>
  <XMLVersion Version="2.00"/>
  <Header>
       <PacketNum>' + @packet + '</PacketNum>
       <Test Choice="No"/>
       <ClientAccountID>' + @clientAcctNum + '</ClientAccountID>
       <ClientUserID>' + @clientUserID + '</ClientUserID>
       <ContactEmail>' + @contactEmail + '</ContactEmail>
       <ContactName>' + @contactName + '</ContactName>
       <ContactPhone>' + @contactPhone + '</ContactPhone>
       <SourceID/>
  </Header>' 
+ (
select top 1
      id, 
      firstName 'Debtors/Names/IndividualName/FirstName',
      lastName 'Debtors/Names/IndividualName/LastName'
from
      r_users
for xml path('Record')
) + '
</Document>'

select @xml

Open in new window

0
Comment
Question by:lrbrister
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 35037384
I'm sure I'm missing something here, but shouldn't line 21 be:
select @xml = '<?xml version="1.0" encoding="UTF-8" ?>

Open in new window

0
 

Author Comment

by:lrbrister
ID: 35037452
kaufmed
That's exactly what I thought...but I'm getting this error...

Msg 9402, Level 16, State 1, Line 21
XML parsing: line 1, character 39, unable to switch the encoding
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35037519
I might be wrong but the ? there sounds like a malformed XML.

Check this link:
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/c58b21b6-cd54-400a-9b40-730965d4b59f
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35037535
so basically try with utf-16

select @xml = '<?xml version="1.0" encoding="UTF-16" ?>
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:lrbrister
ID: 35037587
ralmada:
That ran without an error...but the output had no '<?xml version="1.0" encoding="UTF-16"  tag at the top

It started with "Document"
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 35037742
Not 100% sure, but I understand that if the encoding information is not present, it will use the default, which could be UTF 16 already.

0
 

Author Comment

by:lrbrister
ID: 35037775
ralmada:
 You're correct.
Just found out that the url I post to will have that...I just need the > Document information
0
 

Author Closing Comment

by:lrbrister
ID: 35037777
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now