SQl TO XML

Question

Post reply

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: 95405 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>