SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FOR XML RAW


FOR XML RAW

Author
Message
sql_learner29
sql_learner29
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8155 Visits: 957
Hello all..
I was trying to create an XML which has following design..



<?xml version="1.0"?>
<MAIN-file>
<file name=id type= type size= size/>
<description> [description]</description>
<source type=stype id= [sourceid]/>
<destination type=[accesstype1] id=“[accessid]”/>
<destination type=[accesstype2] id=" [accessid]2” />
<destination type="[accesstype3]" id="[accessid]3”/>
<category name="NEW" </category>
</MAIN-file>



but it has multiple lines with different tags, (elements of XML are in same row)
I was able to get 1st line done with one FOR XML RAW code but not able to get other lines merged..
I have created a sample data here:



IF EXISTS(SELECT * FROM ##temp)
DROP TABLE ##temp

CREATE TABLE ##temp
(
[id] varchar(10) NULL,
[type] [varchar](3) NULL,
[size] [varchar](1) NULL,
[description] [varchar](100) NULL,
[stype] [varchar](6) NULL,
[sourceid] [varchar](9) NULL,
[accesstype] [varchar](13) NULL,
[accessid] varchar(14) NULL,
[Category name] [varchar](3) NULL
)

insert into ##temp


select '4324' , 'xls' , '0', 'SOB' ,'source', 'my source' , 'access_id_tag' , '1268' , 'NEW' union
select '4325' , 'xls' , '0', 'SOB' , 'source', 'my source' , 'access_id_tag' , '1268' , 'NEW' union
select '4698' , 'xls' , '0', 'SOC' , 'source', 'my source' , 'access_id_tag' , '1265' , 'NEW' union
select '4219' , 'xls' , '0', 'COC' , 'source', 'my source' , 'access_id_tag' , '1265' , 'NEW' union
select '4327' , 'xls' , '0', 'SOB' , 'source', 'my source' , 'access_id_tag' , '1268' , 'NEW' union
select '4326' , 'xls' , '0', 'SOB' , 'source', 'my source' , 'access_id_tag' , '1268' , 'NEW' union
select '4092' , 'xls' , '0', 'RMD', 'source' ,'my source' , 'access_id_tag' , '1265' , 'NEW' union
select '4037' , 'xls' , '0', 'SOB' , 'source', 'my source' , 'access_id_tag' , '1265' , 'NEW'



select * from ##temp




SELECT id, type , size
FROM ##temp
FOR XML RAW ('file'), ROOT ('main-file') ;



-- <?xml version="1.0"?>
--<MAIN-file>
-- <file name=id type= type size= size/>
-- <description> [description]</description>
-- <source type=stype id= [sourceid]/>
-- <destination type=[accesstype1] id=“[accessid]”/>
-- <destination type=[accesstype2] id=" [accessid]2” />
-- <destination type="[accesstype3]" id="[accessid]3”/>
-- <category name="NEW" </category>
--</MAIN-file>







Any help with this..

Thanks a lot in advance..


---------------------------------------------------

Thanks

sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98615 Visits: 7281
I'm pretty sure your XML format is not actually valid XML. I don't think you can repeat the same tag at the same level.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
sql_learner29
sql_learner29
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8155 Visits: 957
thanks I thought so too!!
but I am not sure why the format was to be expected like this.

basically we cannot produce this format from XML RAW statement Right?..

<new-file>

<filename="Report 1" type="xlsx" size="666"/>

<filetype="my_id" id=“my office1"/>

<categoryname="My data"> </category>

</new-file>

Let me see what to do now


---------------------------------------------------

Thanks

sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98615 Visits: 7281
sql_learner29 - Friday, March 9, 2018 11:21 AM
thanks I thought so too!!
but I am not sure why the format was to be expected like this.

basically we cannot produce this format from XML RAW statement Right?..

<new-file>

<filename="Report 1" type="xlsx" size="666"/>

<filetype="my_id" id=“my office1"/>

<categoryname="My data"> </category>

</new-file>

Let me see what to do now

I'm not sure you can't create that format, which is entirely different from what you posted earlier with multiple destination tags...


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
sql_learner29
sql_learner29
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8155 Visits: 957
yes I just removed some hard coded data to simplify it..

Can we create this format where I can show like this:
different tag names in same root tag..(with different property?)
like file id, file name and category , subcategory..
Can you provide any sample or how to achieve this..or any link showing like this..?

<new-file>
<filename="Report 1" type="xlsx" size="666"/>
<filetype="my_id" id=“my office1"/>
<categoryname="My data"> </category>
</new-file>



---------------------------------------------------

Thanks

sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98615 Visits: 7281
sql_learner29 - Friday, March 9, 2018 1:33 PM
yes I just removed some hard coded data to simplify it..

Can we create this format where I can show like this:
different tag names in same root tag..(with different property?)
like file id, file name and category , subcategory..
Can you provide any sample or how to achieve this..or any link showing like this..?

<new-file>
<filename="Report 1" type="xlsx" size="666"/>
<filetype="my_id" id=“my office1"/>
<categoryname="My data"> </category>
</new-file>

I may be wrong, but I'm not sure that creating multiple different tags that have sub-values within the same tag and at the same level is "proper xml". I know I don't have the knowledge to do it. Someone else may know. Best resource here that I'm aware of for XML is Eirikur Ericsson..


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
drew.allen
drew.allen
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67347 Visits: 17779
Your big mistake was using the RAW option. That gives you very little control over the output. You're much better off using the PATH option for most purposes or the EXPLICIT option for very fine control. Something like the following. You didn't have multiple accesstypes in your sample data, so I treated the one you did have as two separate ones.

Of course you can have multiple copies of the same elements at the same level. You just have to be careful to somehow separate them.

SELECT id AS [file/@id],
[type] AS [file/@type],
size AS [file/@size],
[description],
stype AS [source/@type],
sourceid AS [source/@sourceid],
accesstype as [destination/@type],
'accessid' AS [destination/@id],
'' AS [*], /* Separator between destination elements. */
accesstype as [destination/@type],
'accessid2' AS [destination/@id]
FROM ##temp
FOR XML PATH('MAIN-file')


Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
drew.allen
drew.allen
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67347 Visits: 17779
sgmunson - Friday, March 9, 2018 11:37 AM
sql_learner29 - Friday, March 9, 2018 11:21 AM
thanks I thought so too!!
but I am not sure why the format was to be expected like this.

basically we cannot produce this format from XML RAW statement Right?..

<new-file>

<filename="Report 1" type="xlsx" size="666"/>

<filetype="my_id" id=“my office1"/>

<categoryname="My data"> </category>

</new-file>

Let me see what to do now

I'm not sure you can't create that format, which is entirely different from what you posted earlier with multiple destination tags...

This is not a valid format. An XML tag must have an element and can optionally have an attribute. Elements have the following formats:
<element />
<element>value</element>


Attribute must occur in the opening tag of an element and have the following format:
attribute="value"


So the following format only contains attributes and is missing an element.
<filename="Report 1" type="xlsx" size="666"/>


It can be fixed in one of the following ways.
<filename type="xlsx" size="666">Report 1</filename>
<newelement filename="Report 1" type="xlsx" size="666"/>


Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
sgmunson
sgmunson
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98615 Visits: 7281
drew.allen - Tuesday, March 13, 2018 12:08 PM
Your big mistake was using the RAW option. That gives you very little control over the output. You're much better off using the PATH option for most purposes or the EXPLICIT option for very fine control. Something like the following. You didn't have multiple accesstypes in your sample data, so I treated the one you did have as two separate ones.

Of course you can have multiple copies of the same elements at the same level. You just have to be careful to somehow separate them.

SELECT id AS [file/@id],
[type] AS [file/@type],
size AS [file/@size],
[description],
stype AS [source/@type],
sourceid AS [source/@sourceid],
accesstype as [destination/@type],
'accessid' AS [destination/@id],
'' AS [*], /* Separator between destination elements. */
accesstype as [destination/@type],
'accessid2' AS [destination/@id]
FROM ##temp
FOR XML PATH('MAIN-file')


Drew

Very cool !!! Just learned a bunch about how to handle XML that's in my category of "horse of a different color". Thanks!


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search