March 8, 2018 at 7:18 am
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,
[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 [/font]
March 8, 2018 at 7:34 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 9, 2018 at 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
Thanks [/font]
March 9, 2018 at 11:37 am
sql_learner29 - Friday, March 9, 2018 11:21 AMthanks 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 9, 2018 at 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>
Thanks [/font]
March 13, 2018 at 9:23 am
sql_learner29 - Friday, March 9, 2018 1:33 PMyes 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2018 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 13, 2018 at 12:21 pm
sgmunson - Friday, March 9, 2018 11:37 AMsql_learner29 - Friday, March 9, 2018 11:21 AMthanks 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
March 13, 2018 at 12:29 pm
drew.allen - Tuesday, March 13, 2018 12:08 PMYour 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply