SQl TO XML

Question

SQl TO XML

js_0505

SSC-Addicted

Points: 411
More actions
April 30, 2010 at 9:06 am

#222079

I am trying to output sql data via XML, I have completed 99% but I need to include a header and I can not find any documentation on how to add a header to XML via SQL.
Here is my current code:
SELECT
-- Map columns to XML attributes/elements with XPath selectors.
esiid AS '@esiId',
SEID AS '@seId',
usertype AS '@userType',
--(
-- Use a sub query for child elements.
-- SELECT
firstName,
lastName,
(
-- Use a sub query for child elements.
SELECT
address,
city,
state,
postalCode,
timeZone
FROM
tbl_Users
FOR
XML PATH ('serviceLocation'), -- The element name for each row.
TYPE -- Column is typed so it nests as XML, not text.
),
emailAddress,
'PortalOnly' as 'hanProfileType',
extendedPANId,
authId
FROM
tbl_Users
--FOR
--XML PATH,
--TYPE
--, -- The element name for each row.
--ROOT('powerSmartUsers') -- The root element name for this result set.
--)
--as testing
--FROM
FOR
XML PATH('utilityUser'), -- The element name for each row.
--XML AUTO,
--XML RAW,
ELEMENTS XSINIL,
ROOT('powerSmartUsers') -- The root element name for this result set.
Here is my result set:
<powerSmartUsers>
<utilityUser esiId="10443720007963940" seId="1" userYype="CA">
<firstname>Green </firstname>
<lastName>Mountain </lastName>
<serviceLocation>
<address>123 Plano Rd</address>
<city>Plano </city>
<state>TX</state>
<postalCode>76137</postalCode>
<timeZone>US/Central</timeZone>
</serviceLocation>
<emailAddress>jason.squires@greenmountain.com</emailAddress>
<hanProfileType>PortalOnly</hanProfileType>
<extendedPANId>7160100008107000</extendedPANId>
<authId>100</authId>
</utilityUser>
</powerSmartUsers>
Here is needed result set with header:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<powerSmartUsers xmlns="http://platform.tendrilinc.com/tnop/extension/ems"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://platform.tendrilinc.com/tnop/extension/ems ../extensions/ems-app/ems-utility-info.xsd"
        count="1">
<utilityUser esiId="10443720007963940" seId="1" userType="CA">
    <firstName>Green</firstName>
    <lastName>Mountain</lastName>
    <serviceLocation>
      <address>123 Plano Rd</address>
      <city>Plano               </city>
      <state>TX</state>
      <postalCode>76137</postalCode>
      <timeZone>US/Central</timeZone>
    </serviceLocation>
    <emailAddress>jason.squires@greenmountain.com</emailAddress>
<hanProfileType>PortalOnly</hanProfileType>
<extendedPANId>7160100008107000</extendedPANId>
    <authId>100</authId>
</utilityUser>
</powerSmartUsers>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Not sure if this does what you need, but check out this link, specifically the XMLNAMESPACES. There's an example about 3/4 of the way down the page.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

js_0505 SSC-Addicted Points: 411 More actions · Answer 2

thanks that has helped a bit. Still having issue though. The serviceLocation element restates the namespaces and I do not want it to. Any additional assistance?

Current code:

WITH XMLNAMESPACES (

DEFAULT

'http://platform.tendrilinc.com/tnop/extension/ems' --AS SQL

, 'www.w3.org/2001/XMLSchema-instance' as "xsi"

, 'http://platform.tendrilinc.com/tnop/extension/emsextensions/ems-utility-info.xsd' as "schemalocation"

)

SELECT

-- Map columns to XML attributes/elements with XPath selectors.

esiid AS '@esiId',

SEID AS '@seId',

usertype AS '@userType',

--(

-- Use a sub query for child elements.

-- SELECT

firstName,

lastName,

(

-- Use a sub query for child elements.

SELECT

address,

city,

state,

postalCode,

timeZone

FROM

tbl_Users

FOR

XML PATH ('serviceLocation'), -- The element name for each row.

TYPE -- Column is typed so it nests as XML, not text.

),

emailAddress,

'PortalOnly' as 'hanProfileType',

extendedPANId,

authId

--INTO testxml

FROM

tbl_Users

--FOR

--XML PATH,

--TYPE

--, -- The element name for each row.

--ROOT('powerSmartUsers') -- The root element name for this result set.

--)

--as testing

--FROM

FOR

XML PATH('utilityUser'), -- The element name for each row.

--XML AUTO,

--XML RAW,

--ELEMENTS XSINIL,

--XMLSCHEMA ('USER'),

ROOT('powerSmartUsers')--,-- The root element name for this result set.

--ELEMENTS

Current Results:

<firstName>Green </firstName>

<lastName>Mountain </lastName>

<address>123 Plano Rd</address>

<city>Plano </city>

<timeZone>US/Central</timeZone>

</serviceLocation>

<emailAddress>jason.squires@greenmountain.com</emailAddress>

<hanProfileType>PortalOnly</hanProfileType>

</utilityUser>

</powerSmartUsers>