Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

XML Workshop - FOR XML PATH Expand / Collapse
Author
Message
Posted Thursday, September 20, 2007 11:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi Ryan,

please post a sample xml structure that you need. I will then try to write a query for that.



.
Post #401008
Posted Friday, September 21, 2007 6:48 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 8:51 AM
Points: 42, Visits: 104

I'm trying to reproduce something like the following:

<compensatingcontrols xmlns ="http://www.namespace.net/schema.xsd">
  <compensatingcontrol>
    <id>4</id>
    <name>Control1</name>
    <description />
    <version />
    <source>Source Name</source>
    <controltext>Some control text.</controltext>
    <status>enabled</status>
    <validfrom>2007-08-03</validfrom>
    <expirationflag>neverexpires</expirationflag>
    <expiration>1900-01-01</expiration>
    <lastupdatedby>OWNER</lastupdatedby>
    <lastupdatedon>2007-09-17</lastupdatedon>
    <createdby>OWNER</createdby>
    <createdon>2007-08-03</createdon>
    <additionalinfo>
      <notes xmlns="http://www.namespace.net/schema.xsd" />
    </additionalinfo>
    <ownerinfo>
      <profile>
        <id>3</id>
        <profilename>OWNER</profilename>
      </profile>
    </ownerinfo>
  </compensatingcontrol>
</compensatingcontrols>

I can produce everything but the root @xmlns and the notes @xmlns attributes using the following query:

SELECT compensatingcontrol.ControlID AS 'compensatingcontrol/id'
 , compensatingcontrol.[Control Name] AS 'compensatingcontrol/name'
 , NULL AS 'compensatingcontrol/description'
 , NULL AS 'compensatingcontrol/version'
 , 'START' AS 'compensatingcontrol/source'
 , compensatingcontrol.[Control Description] AS 'compensatingcontrol/controltext'
 , 'compensatingcontrol/status' =
  CASE compensatingcontrol.[Current?]
   WHEN 'True' THEN 'enabled'
   ELSE 'disabled'
  END
 , '2007-01-01' AS 'compensatingcontrol/validfrom'
 , 'neverexpires' AS 'compensatingcontrol/expirationflag'
 , '1900-01-01' AS 'compensatingcontrol/expiration'
 , 'GRMalone' AS 'compensatingcontrol/lastupdatedby'
 , '2007-06-01' AS 'compensatingcontrol/lastupdatedon'
 , 'GRMalone' AS 'compensatingcontrol/createdby'
 , '2007-01-01' AS 'compensatingcontrol/createdon'
 , NULL AS 'compensatingcontrol/additionalinfo/notes'
 , profile.EmpNum AS 'compensatingcontrol/ownerinfo/profile/id'
 , profile.EmpUserName AS 'compensatingcontrol/ownerinfo/profile/profilename'
FROM dbo.vwControls compensatingcontrol LEFT OUTER JOIN
 dbo.ProcessControl ownerinfo ON compensatingcontrol.ControlID = ownerinfo.ControlID LEFT OUTER JOIN
 dbo.vwProcessOwners [profile] ON ownerinfo.ProcessID = [profile].ProcessID
FOR XML PATH('')
 , TYPE
 , ELEMENTS XSINIL
 , ROOT('compensatingcontrols');

Thanks for your help! These namespaces are really giving me grief.

Post #401114
Posted Friday, September 21, 2007 11:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Ryan,

run this query:

(excuse the bad formatting)

 

WITH XMLNAMESPACES (

DEFAULT 'http://www.namespace.net/yourschema.xsd'

)

SELECT 'compensation control ID' AS 'compensatingcontrol/id' ,

'compensation control name' AS 'compensatingcontrol/name' ,

NULL AS 'compensatingcontrol/description' ,

NULL AS 'compensatingcontrol/version' ,

'START' AS 'compensatingcontrol/source' ,

'compensation control description' AS 'compensatingcontrol/controltext' ,

'true' as 'compensatingcontrol/status',

'2007-01-01' AS 'compensatingcontrol/validfrom' ,

'neverexpires' AS 'compensatingcontrol/expirationflag' ,

'1900-01-01' AS 'compensatingcontrol/expiration' ,

'GRMalone' AS 'compensatingcontrol/lastupdatedby' ,

'2007-06-01' AS 'compensatingcontrol/lastupdatedon' ,

'GRMalone' AS 'compensatingcontrol/createdby' ,

'2007-01-01' AS 'compensatingcontrol/createdon' ,

(SELECT 'this is a note'

FOR XML PATH('notes'), TYPE

) as 'compensatingcontrol/additionalinfo',

--NULL AS 'c:compensatingcontrol/additionalinfo/notes' ,

'empnum' AS 'compensatingcontrol/ownerinfo/profile/id' ,

'empusername' AS 'compensatingcontrol/ownerinfo/profile/profilename'

FOR XML PATH('') , TYPE , ELEMENTS XSINIL , ROOT('compensatingcontrols');

 

this will give the following results:

<compensatingcontrols xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.namespace.net/yourschema.xsd">

<compensatingcontrol>

<id>compensation control ID</id>

<name>compensation control name</name>

<description xsi:nil="true" />

<version xsi:nil="true" />

<source>START</source>

<controltext>compensation control description</controltext>

<status>true</status>

<validfrom>2007-01-01</validfrom>

<expirationflag>neverexpires</expirationflag>

<expiration>1900-01-01</expiration>

<lastupdatedby>GRMalone</lastupdatedby>

<lastupdatedon>2007-06-01</lastupdatedon>

<createdby>GRMalone</createdby>

<createdon>2007-01-01</createdon>

<additionalinfo>

<notes xmlns="http://www.namespace.net/yourschema.xsd">this is a note</notes>

</additionalinfo>

<ownerinfo>

<profile>

<id>empnum</id>

<profilename>empusername</profilename>

</profile>

</ownerinfo>

</compensatingcontrol>

</compensatingcontrols>



.
Post #401384
Posted Thursday, March 20, 2008 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 2:09 PM
Points: 6, Visits: 31
In case you are still wondering about this, you can use the "WITH XMLNAMESPACES" clause immediately prior to the SELECT clause, and you can add as many namespaces as you need. An example would be:

WITH XMLNAMESPACES ('http://www.mynamespaceurl2.com' as "myprefix2", 'http://www.mynamespaceurl1.com' as "myprefix1", DEFAULT 'http://www.mydefaultnamespaceurl.com')

SELECT...

You then build the select and sub-select statements, using the prefixes you establish in your namespace clause in the AS clause of each field specification, such as "SELECT Field1 AS 'myprefix1:Field1'" and so on.

Hope this helps.
Post #472420
Posted Monday, April 19, 2010 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:56 PM
Points: 5, Visits: 364
Hi Jacob,

How to add a complex element in XML.
For ex:
Let say i have my XML like this
<Business>
<Business_ID>12345</Business_Id>
<Account>
<ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER>
<ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION>
<ACCOUNT_TYPE>C</ACCOUNT_TYPE>
</Account>
<Account>
<ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER>
<ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION>
<ACCOUNT_TYPE>S</ACCOUNT_TYPE>
</Account>
</Business>


And i want the result to look like this

<Business>
<Business_ID>12345</Business_Id>
<Accounts>
<Account>
<ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER>
<ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION>
<ACCOUNT_TYPE>C</ACCOUNT_TYPE>
</Account>
<Account>
<ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER>
<ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION>
<ACCOUNT_TYPE>S</ACCOUNT_TYPE>
</Account>
</Accounts>
</Business>

I have added here complex type Accounts. Can you tell me how to change my T sql to add this complex element.
Post #906154
Posted Tuesday, April 20, 2010 12:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Try this:
DECLARE @t TABLE (
Acno VARCHAR(20),
acdesc VARCHAR(20),
actype CHAR(1)
)
INSERT INTO @t (acno, acdesc, actype)
SELECT '1122334', 'Tesr acct', 'C' UNION ALL
SELECT '0004455', 'Bank Test Account', 'S'

SELECT
'12345' AS Business_ID,
(
SELECT
acno AS ACCOUNT_NUMBER,
acdesc AS ACCOUNT_DESCRIPTION,
actype AS ACCOUNT_TYPE
FROM @t
FOR XML PATH('Account'),ROOT('Accounts'), TYPE
)
FOR XML PATH(''), ROOT('Business')
/*
<Business>
<Business_ID>12345</Business_Id>
<Accounts>
<Account>
<ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER>
<ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION>
<ACCOUNT_TYPE>C</ACCOUNT_TYPE>
</Account>
<Account>
<ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER>
<ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION>
<ACCOUNT_TYPE>S</ACCOUNT_TYPE>
</Account>
</Accounts>
</Business>
*/



.
Post #906572
Posted Tuesday, April 20, 2010 9:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:56 PM
Points: 5, Visits: 364
Jacob, Sorry for not giving all the details.
Here it is.
The business information is in one table and Account info is in second table.
I need to join these two tables to get the result.

Business table Structure

Crate table Business
(
Business_ID varchar(50),
Company Name varchar(50),
Address_Line1 varchar(50),
City varchar(50),
State char(2))


Let say the data looks like this in this table

Business_ID Business_name Address City State
12345 ABC St1 Dallas TX
12356 HP ST2 Austin TX





Account Table Structure
(
Business_ID varchar(50),
Account_ID Varchar(50),
Account_Number varchar(50),
Description Varchar(50),
Account_Type char(1)
)

Business_ID Account_ID Account_Number Description Account_Type
12345 111 5678 Corporation C
12345 111 4567 business Checking C
12345 111 7655 Basic Savings S
12356 122 7788 Personal Checking C
12356 122 8899 Checking C


My XML should look like this

<XMl xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Business_Type>
<Small_Business>
<Business_ID> 12345 </Business_ID>
<Business_Name> ABC </Business_Name>
<Address1> St1 </Address1>
<City> Dallas </City>
<State> TX </State>
<Accounts>
<Account>
<Account_Number>5678 </Account_Number>
<Description> Corporation<Description>
<Account_Type> C</Account_Type>
</Account>
<Account>
<Account_Number> 4567</Account_Number>
<Description> business Checking<Description>
<Account_Type> C</Account_Type>
</Account>
<Account>
<Account_Number> 7655</Account_Number>
<Description>Basic Savings <Description>
<Account_Type>S </Account_Type>
</Account>
</Accounts>
<Business_ID> 12356</Business_ID>
<Business_Name> HP </Business_Name>
<Address1>ST2 </Address1>
<City>Austin </City>
<State> TX</State>
<Accounts>
<Account>
<Account_Number>7788</Account_Number>
<Description>Personal Checking <Description>
<Account_Type>C </Account_Type>
</Account>
<Account>
<Account_Number>8899 </Account_Number>
<Description>Checking <Description>
<Account_Type> C</Account_Type>
</Account>
</Accounts>
</Small_Business>
<Business_Type>
</XMl>


Here the complex elements are
BusinessType, Small_Business and Accounts . I am having difficulties to have these elements in my XML.
Jacob, This is only sample of my whole XML. I have got a Very Complex XSD with many complex and simple elements. I have Data in 10 tables that i need to pull information from, which means i should use 10 joins. I need to generate an XML from these tables. So i was just trying to see if it is possible in SQL Server.
Post #906994
Posted Tuesday, April 20, 2010 8:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
try this
SELECT (
select
*,
(
select * from account a
where a.business_id = b.business_id
for xml path('account'), root('accounts'), type
)
from business b
for xml path(''), root('Small_Business'),TYPE
)
FOR XML PATH('Business_Type'), ROOT('XMl')



.
Post #907381
Posted Thursday, April 22, 2010 8:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:56 PM
Points: 5, Visits: 364
Thanks a lot Jacob, its working..
Post #909125
Posted Monday, February 14, 2011 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:42 AM
Points: 5, Visits: 18
Nice Article. The last two workarounds can be achieved by a statement as follows. No need of workarounds

SELECT
Country.CountryName AS [name],
Country.Currency,
City.CityName AS [name],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name],
Customer.Phone
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
ORDER BY CountryName, CityName
FOR XML AUTO, root('CustomersByRegion')

Root function gives a way to assign a name for the root element.
Post #1063638
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse