Output xml file from MS SQL Server 2005 stored procedure

Databases
1

[FONT=Verdana][SIZE=2]I have been working on a stored procedure that outputs an xml file for use on a web site. Here’s the stored procedure (please note some field names and values have been edited to protect client).

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[PH_PROC_MYDATA_XML]

AS 

DROP TABLE PH_TMP_MYDATA_XML

CREATE TABLE PH_TMP_MYDATA_XML
(ID INT,DOCUMENT XML)

INSERT INTO PH_TMP_MYDATA_XML (ID)
VALUES (1)
UPDATE PH_TMP_MYDATA_XML

SET DOCUMENT=(    
    SELECT
     '156' AS '@id',
     'en-gb' AS '@lang',
     'Business info' AS 'name',
     'http://www.blahblahblahsite.com' AS 'url',
     'do we want a description?' AS 'description',
     '<![CDATA[ some stuff will have to go in here]]>' AS 'infotxt',
        (SELECT
          BUS_ID AS '@id',
          Name AS 'name',
          'http://www.blahblahblahsite.com/index.php?bus='+BUS_ID+'&othercode='+OTHERCODE AS 'site-url',
            (SELECT
             'email' AS 'contact/@type',
             BUSEmail AS 'contact'--,
             --'telephone' AS 'contact/@type',
             --'0000 000 000' AS 'contact'
            FOR XML PATH('contact-details'),TYPE),
            (SELECT
              BUSID AS '@id',
              JOB_TITLE+' '+FIRST_NAME+' '+SURNAME AS 'name',
              TYPE AS 'rank',
              Comment AS 'bio',
              'http://www.blahblahblahsite.com/images/photos/'+BUSID+'.jpg' AS 'photo-url'
            FROM VW_Team_Names
            WHERE VW_Team_Names.BUS = VW_BUSInfoLink_ForXML_v2.BUS_ID
            FOR XML PATH('member'), TYPE, ROOT('members')),
            (SELECT
              Title AS 'issue',
              AddDate AS 'issue-date',
              Entry AS 'action',
              ActionDate AS 'action-date'
            FROM VW_Priorities_for_XML
            WHERE (VW_Priorities_for_XML.BUS = VW_BUSInfoLink_ForXML_v2.BUS_ID)
            FOR XML PATH('priority'), TYPE, ROOT('priorities'))
        FROM VW_BUSInfoLink_ForXML_v2
        WHERE BUS_ID <> '0'
        FOR XML PATH('team'),TYPE, ROOT('teams'))
    FOR XML PATH('business'),TYPE
)
WHERE ID=1

DECLARE @COMMAND NVARCHAR(400)
SET @COMMAND='bcp "SELECT TOP 1 DOCUMENT FROM BUS_V3.dbo.PH_TMP_MYDATA_XML" queryout \\\\someservername\\d$\\intepub\\wwwroot\\busdata.xml -Sserverval -Uusernameval -Ppasswordval -c -r -t'
SELECT @COMMAND
EXEC master..xp_cmdshell @COMMAND

Everything is working absolutely fine apart from the bit that is commented out (the lines prefixed with --):

(SELECT
‘email’ AS ‘contact/@type’,
BUSEmail AS ‘contact’–,
–‘telephone’ AS ‘contact/@type’,
–‘0000 000 000’ AS ‘contact’
FOR XML PATH(‘contact-details’),TYPE),

as soon as I try to execute when doing more than 1 contact type= I get the following error message:

Msg 6852, Level 16, State 1, Procedure PH_PROC_MYDATA_XML, Line 14
Attribute-centric column ‘contact/@type’ must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

The output I want in the xml is something like:

<contact-details>
<contact type=“telephone”>1111 111 1111</contact>
<contact type=“web”>http://www.sdr34ree34.com</contact>
</contact-details>

So please forgive me if I am using incorrect terminology but how do I set different multiple attributes for sibling nodes?[/SIZE][/FONT]

Any help would be greatly appreciated.

Many thanks for your time.

2

Well not many response to this lol!

With quite a bit of fiddling I have managed to do it:

(SELECT
    (SELECT
        'email' AS '@type',
         BUSEmail AS 'text()'
     FOR XML PATH('contact'), TYPE),
     (SELECT
         'telephone' AS '@type',
         '0000 000 000' AS 'text()'
     FOR XML PATH('contact'), TYPE)
 FOR XML PATH('contact-details'), TYPE),

Maybe someone will find this useful one day???

3

Hi, thanks for the info

I`m trying to make a stp myself for exporting a XML to a system file.
I tried to do thid bij stp make web task. the problem in this is formating the xml(It looks like it cannot be done)

When i was looking for another solution i came on this page.
I think it is a verry good solution! Bud there are some thins in the stp i don’t understand.
Like:
bcp “SELECT TOP 1 DOCUMENT FROM dbo.PH_TMP_MYDATA_XML” queryout \\MyServer\c$\intepub\wwwroot\busdata.xml -S MyServer -U sa -P ***** -c -r -t

I am getting the following error output:

SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name ‘dbo.PH_TMP_MYDATA_XML’.
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL

Here is the complete stp…maybe anyone can help me with solving this?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[PH_PROC_MYDATA_XML]

–EXECUTE [dbo].[PH_PROC_MYDATA_XML]

AS

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[PH_TMP_MYDATA_XML]‘) AND type in (N’U’))
DROP TABLE [dbo].[PH_TMP_MYDATA_XML]

Create TABLE dbo.PH_TMP_MYDATA_XML
(ID INT,DOCUMENT XML)

INSERT INTO dbo.PH_TMP_MYDATA_XML (ID)
VALUES (1)
UPDATE dbo.PH_TMP_MYDATA_XML

SET DOCUMENT=(Select USERS.Userid as “Properties/userID”,
Cast(USERS.Roepnaam as Nvarchar(Max)) as “Properties/First_Name”,
Cast(Isnull(USERS.LastName,‘’) + isnull(‘, ’ + USERS.Initials, ‘’) as Nvarchar(Max)) “Properties/Last_Name”,
Cast(isnull(USERS.Initials,’‘) as Nvarchar(Max)) as “Properties/Initials”,
Cast(Rtrim(Isnull(USERS.Lastname,’‘) + isnull(’, ’ + USERS.Firstname,‘’) + ’ ’ + isnull(USERS.Firstname, ‘’)) as Nvarchar(Max)) as “Properties/Dispay_Name”,
Cast(LOWER(USERS.UserID) as Nvarchar(Max)) as “Properties/User_logon_name”,
Cast(Replace(isnull(USERS.FirstName,‘’) + ’ ’ + Isnull(USERS.Prefix,‘’) + ’ ’ + Isnull(USERS.LastName,‘’), ’ ‘, ’ ‘) as Nvarchar(Max)) as “Properties/Description”,
Cast(Isnull(USERS.First,’’)as Nvarchar(Max)) as “Properties/Customfield1”,
Cast(‘’ as Nvarchar(Max)) as “Properties/SamAccountName”,
Cast(‘@mail.local’ as Nvarchar(Max)) as “Properties/UserPrincipalName”,
Cast(isnull(A.Street,‘’) + ’ ’ + Cast(isnull(A.Housenumber,‘’ ) as VArchar(110)) as Nvarchar(Max)) as “Properties/Street”,
Cast(‘’ as Nvarchar(Max)) as “Properties/PO_Box”,
Cast(A.City as Nvarchar(Max)) as “Properties/City_Province”,
Cast(‘’ as Nvarchar(Max)) As “Properties/State_Province”,
Cast(UPPER(A.Postal) as Nvarchar(Max)) as “Properties/ZIP_Postal_Code”,
Cast(LA.Code as Nvarchar(Max)) as “Properties/Country_Region”,
Cast(isnull(TF.FunctName,‘’) as Nvarchar(Max)) as “Properties/Title”,
Cast(B.Name as Nvarchar(Max)) as “Properties/Company”,
Cast(T.Code as Nvarchar(Max)) as “Properties/Department”,
Cast(‘’ as Nvarchar(Max)) as “Properties/Office”,
Cast(Case when Len(Isnull(TW.phone,USERS.phone)) = 10 then ‘+32’ + Right(Isnull(TW.Telefoon,USERS.phone),9) else Isnull(TW.phone,USERS.phone) end as Nvarchar(Max)) as “Properties/Phone”,
Cast(‘’ as Nvarchar(Max)) as “Properties/Manager”,
Cast(‘’ as Nvarchar(Max)) as “Properties/Fax”,
Cast(‘’ as Nvarchar(Max)) as “Properties/Mobile”,
Cast(‘’ as Nvarchar(Max)) as “Properties/Pager”,
Cast(‘’ as Nvarchar(Max)) as “Properties/IP_Phone”,
Cast(‘’ as Nvarchar(Max)) as “Properties/Fysical_Delivery_Office_Name”
From Employee"USERS"
Join TWM TW on TW.Employee = USERS.Number
Join Team T on TW.Team = T.Number
Join DeploymentGroupUG on TW.DeploymentGroup = UG.Number
Join Chunk C on UG.Chunk = C.Number
Join Department D on C.Divisie = D.Number
Join Company B on D.Company = B.Number
Left Join Location L on USERS.Location = L.Number
Left Join Adres A on L.Adres = A.Nummer
Left Join Land LA on A.Land = LA.Nummer
Left Join TeamFunction TF on TW.TeamFunction = TF.Number
For XML PATH(‘USER’), ROOT(‘USERS’)
)
WHERE ID=1

Hope you can help?

Kind regarts,

Mark

DECLARE @COMMAND NVARCHAR(400)
SET @COMMAND=‘bcp “SELECT TOP 1 DOCUMENT FROM dbo.PH_TMP_MYDATA_XML” queryout \\MyServer\c$\intepub\wwwroot\busdata.xml -S MyServerName -U sa -P password -c -r -t’
SELECT @COMMAND
EXEC master…xp_cmdshell @COMMAND

4

I Allready solved it, the output works fine…Thanks for the solution! :slight_smile: I have one question…How can you format de XML by making a new line after eatch end tag?

Kind regarts,

Mark