Export Report to File: Export the data as an XML file?

I have two tables in my database like this.

AppsTable with columns INSTANCE_NAME, VALUE.

SecondTable with columns UID,SID,XID

I need the xml file like below.

- -

where commonID is the Value of UID from the second table,

appInstance name is the instance_name from the appstable.

If i have values like this in second table:

SID UID XID

XAB ABC AB2

BAX 23D BCK

I need the xml file like this.

</appInstance>

- <appInstance name="sap">

<appID commonID="ABC">AB2appID>

</appInstance>

please help me.

From your explanations it is not clear to me what you are trying to do.

Can you provide a query that gives the following result:

Siebel ABC XAB 23D BAX

sap ABC AB2 23D BCK

Then formatting it as XML will be a FOR XML application.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hai Eugene,

Here i am giving the code what i have done for my requirement.

SELECT name,(SELECT * FROM TempTable_XREF

FOR XML AUTO,TYPE).query(' for $p in /TempTable_XREF

return

{data($p/@.UCM_UID)} --here the modification needed.

</APPID>

FROM AppsTable as APPINSTANCE

FOR XML AUTO

In the above query in place of @.ucm_uid i need one different value for each loop

In other words, I have 4 columns in my database table UCM_UID,S_ID, IX_ID,COMMON_ID

For the first time i need UCM_UID

For the second time i need S_ID

For the third time i need IX_ID

|||

What I didn’t understand was the relationship between the two tables you use. Without that I can’t interpret “the first time”, “the second time”, and “the third time”.

Do you really want to get a cross product of the two tables and format it as XML? Did you intend to add into your sub-query a correlation condition to the outer query?

As for the formatting part, did you mean something like

SELECT

name,

(SELECT * FROM TempTable_XREF FOR XML AUTO,TYPE)

.query('for $p in /TempTable_XREF

return

{data($p/@.UCM_UID)}

</APPID>

{data($p/@.S_ID)}

</APPID>

{data($p/@.IX_ID)}

</APPID>

FROM AppsTable as APPINSTANCE

FOR XML AUTO

Or the equivalent using purely FOR XML functionality:

SELECT

name,

(SELECT

COMMON_ID AS "APPID/@.CommonID",

UCM_UID AS "APPID/text()",

NULL AS dummy1,

COMMON_ID AS "APPID/@.CommonID",

S_ID AS "APPID/text()",

NULL AS dummy2,

COMMON_ID AS "APPID/@.CommonID",

IX_ID AS "APPID/text()"

FROM TempTable_XREF FOR XML PATH(''),TYPE)

FROM AppsTable FOR XML RAW('APPINSTANCE')

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Hai Eugene,

the following format i need my XML file.

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

- <listOfIDXRefData xmlns="http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon">

- <idXRef name="BUS UNIT">

- <APPINSTANCE name="Siebel_80">

</APPINSTANCE>

- <APPINSTANCE name="Siebel_UCM">

</APPINSTANCE>

- <APPINSTANCE name="IXMAL_01">

</APPINSTANCE>

</IdxRef>

</listOfIDXRefData>

- <appInstance name="Siebel_80">

<<appID commonID="Pay-1-3EY">1-SIEB_3EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_Y</appID>

</appInstance>

- <appInstance name="Siebel_UCM">

<<appID commonID="Pay-1-3EY">1-UCM_Y</appID>

<<appID commonID="Pay-1-3EY">1-UCM_EY</appID>

</appInstance>

- <appInstance name="IXMAL_01">

<<appID commonID="Pay-1-3EY">1-IX_EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_Y</appID>

</appInstance>

- <appInstance name="SAP46C_01">

</appInstance>

- <appInstance name="SiebelSIA75_01">

</appInstance>

- <appInstance name="SiebelSIA75_02">

</appInstance>

</idXRef>

</listOfIDXRefData>

Common id should be appended text (like cust or pay) with ucm id.

|||

Hai Eugene,

the following format i need my XML file.

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

- <listOfIDXRefData xmlns="http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon">

- <idXRef name="BUS UNIT">

- <APPINSTANCE name="Siebel_80">

</APPINSTANCE>

- <APPINSTANCE name="Siebel_UCM">

</APPINSTANCE>

- <APPINSTANCE name="IXMAL_01">

</APPINSTANCE>

</IdxRef>

</listOfIDXRefData>

- <appInstance name="Siebel_80">

<<appID commonID="Pay-1-3EY">1-SIEB_3EY</appID>

<<appID commonID="Pay-1-3EY">1-SIEB_Y</appID>

</appInstance>

- <appInstance name="Siebel_UCM">

<<appID commonID="Pay-1-3EY">1-UCM_Y</appID>

<<appID commonID="Pay-1-3EY">1-UCM_EY</appID>

</appInstance>

- <appInstance name="IXMAL_01">

<<appID commonID="Pay-1-3EY">1-IX_EY</appID>

<<appID commonID="Pay-1-3EY">1-IX_Y</appID>

</appInstance>

- <appInstance name="SAP46C_01">

</appInstance>

- <appInstance name="SiebelSIA75_01">

</appInstance>

- <appInstance name="SiebelSIA75_02">

</appInstance>

</idXRef>

</listOfIDXRefData>

Common id should be appended text (like cust or pay) with ucm id.

|||

Hai Eugene..

Here i am specifying the entire requirement.

I have two tables
one with appinstance name,appid.
secondwithucmid,siebelid,ixid.(there are many tables i have with these cols)

note:I have many tables with these 3 id columns.

In my xml file

The element <idxRef name=" CUSTOMER">
The customer here is my table name
Each tag of this type specifies different tables.

The element <appInstance name="siebel_80">
-this is the value from the first table.

As i have 3 ids in my table , here the common id would be
some appended text with any id from those 3 values.

HOPE YOU UNDERSTOOD MY REQUIREMENT ,
IF CAN'T PLEASE REPLY.

|||

First you should write a SQL query that gives you the following result.

BUS UNIT Siebel_80 Cust_1-UCM-123001 1-SIEB-123001

BUS UNIT Siebel_80 Cust_1-UCM-123002 1-SIEB-123002

BUS UNIT Siebel_80 Cust_1-UCM-123003 1-SIEB-123003

BUS UNIT Siebel_UCM Cust_1-UCM-123001 1-UCM-123001

BUS UNIT Siebel_UCM Cust_1-UCM-123002 1-UCM-123002

BUS UNIT Siebel_UCM Cust_1-UCM-123003 1-UCM-123003

BUS UNIT IXMAL_01 Cust_1-UCM-123001 1-IX-123001

BUS UNIT IXMAL_01 Cust_1-UCM-123002 1-IX-123002

BUS UNIT IXMAL_01 Cust_1-UCM-123003 1-IX-123003

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_EY

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_3EY

PAYMENT Siebel_80 Pay-1-3EY 1-SIEB_Y

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_3EY

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_Y

PAYMENT Siebel_UCM Pay-1-3EY 1-UCM_EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX3EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX_EY

PAYMENT IXMAL_01 Pay-1-3EY 1-IX_Y

PAYMENT SAP46C_01 Pay-1-3EY 0001

PAYMENT SiebelSIA75_01 Pay-1-3EY 1-3EY

PAYMENT SiebelSIA75_02 Pay-1-3EY 1-3EY

Then such query can be transformed into a FOR XML query that formats the result as XML in the desired shape. You’ll have to use new FOR XML features of SQL Server 2005. See my post above for examples and also look at FOR XML articles in BOL. Use WITH XMLNAMESPACES to add XML namespace into XML results. Let me know if you need any help with FOR XML query.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Fine Eugene, I got your point.

But i am not able to get my result in a single sql query

I have written the following queries to get the format what you have specified in the above reply.

SELECT AppsTable.name,TempTable_XREF.UCM_UID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='Siebel_UCM'

SELECT AppsTable.name,TempTable_XREF.SIEBEL_ID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='Siebel_80'

SELECT AppsTable.name,TempTable_XREF.IXMAL_ID,'CUST_'+ TempTable_XREF.UCM_UID AS COMMON_ID FROM TempTable_XREF,AppsTable where AppsTable.name='IXMAL_01'

I have referred SQLSERVER DOC but i am not able to write the single query by using ' FOR XML ' clause. Please Reply how to get the same result in XML for the above queries using the FOR XML clause in one sql query.

Thanks,

|||

You could have used UNION ALL between the three queries ;-)

Here’s a draft FOR XML query for the three queries you provided using SQL Server 2005 sub-query FOR XML and PATH mode:

WITH XMLNAMESPACES (DEFAULT 'http://www.siebel.com/uan/SiebelBIAs/SharedComponents/CommonObjects/coCommon')

SELECT

'BUS UNIT' AS "idXRef/@.name",

'Siebel_UCM' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.UCM_UID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='Siebel_UCM'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*",

NULL AS "idXRef/DummySeparator1",

'Siebel_80' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+ TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.SIEBEL_ID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='Siebel_80'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*",

NULL AS "idXRef/DummySeparator2",

'IXMAL_01' AS "idXRef/APPINSTANCE/@.name",

(SELECT

'CUST_'+ TempTable_XREF.UCM_UID AS "@.CommonID",

TempTable_XREF.IXMAL_ID AS "text()"

FROM TempTable_XREF, AppsTable where AppsTable.name='IXMAL_01'

FOR XML PATH('APPID'), TYPE) AS "idXRef/APPINSTANCE/*"

FOR XML PATH('listOfIDXRefData')

I can’t verify the syntax so the query may contain parse errors.

You’ll need to add your ordering if necessary.

Note that the result will be in UTF-16 encoding and there will be no XML declaration. If you need XML declaration you’ll have to add it separately.

If you need this kind of XML formatting in SQL Server 2000 environment you’ll need to use FOR XML EXPLICIT.

You may want to take a look at your data modeling side of story – I don’t see much sense in AppsTable in the FROM clause of your queries.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

The code samples were not verified and may contain syntax errors.

|||

Thank u verymuch Eugene,

Atlast i got the solution from your side.

Eugene, After running the above query , it is showing the namespace for each and every tag ( eg: <appid xmlns="http://..........")

How can i resolve this problem. Except this everything is ok.

Thanks,

Rao.

|||

Rao,

Unfortunately, there’s no way to remove the extraneous XML namespace declarations in SQL Server 2005. They add verbosity but do not change XML content in most XML application data models. You could work it around with string concatenations but it would lead to highly unmaintainable code.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Got your point thanks Eugene.

Regards,

Rao.

|||

Hai Eugene,

To generate the XML file,

Currently we are working with sample database which contains less number of records, as the process going on we will have lakhs of records with in the database.

Will the same query be ok for that also? or it leads to any performance issues.

Sunday, February 19, 2012

Export the data as an XML file?

No comments:

Post a Comment

Export Report to File

Blog Archive

About Me