XML PROBLEMS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

XML PROBLEMS

Author

Topic

crh88
Starting Member

14 Posts

Posted - 2014-01-14 : 10:28:30

IMAGE 1
http://imagizer.imageshack.us/v2/800x600q90/824/dao3.jpg

IMAGE 2
http://imagizer.imageshack.us/v2/800x600q90/690/2wxs.jpg

IMAGE 3
http://imagizer.imageshack.us/v2/800x600q90/834/4ku3.jpg

IMAGE 4
http://imagizer.imageshack.us/v2/800x600q90/809/9qdt.jpg

Basically I need the correct code in order to shred the XML table thats been pushed into my new SQL Database so I can view it in columns and rows

I have attached screenshots of every stage so far in the process – Sorry I have had to restrict some sensitive restricted infoprmation due to the nature of my work – but the structure is clearly displayed.

IMAGE 1 = The Query which Created the database (OPENXMLTesting) and Table structure and pushed the Raw XML data into the SQL Table

IMAGE 2 = The Resulting XML Code thats been pushed into SQL due to Query 1.

IMAGE 3 = The Query I am trying to use to shred the XML code into Columns and Rows
(Key parts of this query are the inputs next to SELECT statement (COLUMNS - In Black text) AND FROM OPENXML (The XML Tree hierarchy- In Red)

Every input I have tried so far throws up an error code.
QUESTION MARKS ARE THERE BECAUSE I AM UNSURE WHAT TO SELECT IN THESE QUERIES TO DISPLAY THE DATA CORRECTLY WITHOUT ERROR, WHAT ARE THE CORRECT COLUMN NAMES AND ROOT DIRECTORY?

IMAGE 4 = AND FINALLY A CLEANER VIEW OF THE XML TREE SHOWING THE STRUCTURE

Any Help greatly appreciated.

Kind Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:54:59

sorry cant read xml structure clearly from images. can you post small part of xml showing required nodes and explain what data you want out of them? that would be make it easier for us to understand and help you out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

crh88
Starting Member

14 Posts

Posted - 2014-01-16 : 10:03:04

<model-responses>
- <model mh="0x30061c0">
<attribute id="0x5006e">one</attribute>
<attribute id="0x50030"/>
<attribute id="0x33000e">two</attribute>
<attribute id="0x22564">three</attribute>
<attribute id="0xhhf0002e" error="NoSuchAttribute" />
</model>
- <model mh="0x30006c0">
<attribute id="0x5006e">four</attribute>
<attribute id="0x50030">five</attribute>
<attribute id="0x33000e">six</attribute>
<attribute id="0x22564">seven</attribute>
<attribute id="0xhhf0002e" />

Okay I do hope this helps - if not I can post more via PM?

I have changed the restricted information into numbers for this example.

So what I need in SQL Rows and Columns would be the attribute id's in the header and then the values in the rows, with a separate column for all attribute ID's

I think I am close with my code (in the images) so maybe a tweak of what I have.

Kind Regards,

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-01-16 : 12:08:51

quote:
Originally posted by crh88

<model-responses>
- <model mh="0x30061c0">
<attribute id="0x5006e">one</attribute>
<attribute id="0x50030"/>
<attribute id="0x33000e">two</attribute>
<attribute id="0x22564">three</attribute>
<attribute id="0xhhf0002e" error="NoSuchAttribute" />
</model>
- <model mh="0x30006c0">
<attribute id="0x5006e">four</attribute>
<attribute id="0x50030">five</attribute>
<attribute id="0x33000e">six</attribute>
<attribute id="0x22564">seven</attribute>
<attribute id="0xhhf0002e" />

Okay I do hope this helps - if not I can post more via PM?

I have changed the restricted information into numbers for this example.

So what I need in SQL Rows and Columns would be the attribute id's in the header and then the values in the rows, with a separate column for all attribute ID's

I think I am close with my code (in the images) so maybe a tweak of what I have.

Kind Regards,

Here is an example of how you can shred it:

DECLARE @x XML = '
<model-responses>
  <model mh="0x30061c0">
    <attribute id="0x5006e">one</attribute>
    <attribute id="0x50030" />
    <attribute id="0x33000e">two</attribute>
    <attribute id="0x22564">three</attribute>
    <attribute id="0xhhf0002e" error="NoSuchAttribute" />
  </model>
  <model mh="0x30006c0">
    <attribute id="0x5006e">four</attribute>
    <attribute id="0x50030">five</attribute>
    <attribute id="0x33000e">six</attribute>
    <attribute id="0x22564">seven</attribute>
    <attribute id="0xhhf0002e" />
  </model>
</model-responses>'
SELECT
	c1.value('./@mh','varchar(32)') AS ModelMH,
	c2.value('./@id','varchar(32)') AS AttributeId,
	c2.value('.','varchar(32)') AS AttributeValue
FROM
	@x.nodes('//model') T1(c1)
	CROSS APPLY c1.nodes('./attribute') T2(c2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 14:11:33

for making them into separate columns you need pivot operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

crh88
Starting Member

14 Posts

Posted - 2014-01-16 : 15:27:13

Thanks James the formula shredded the data :)

As Visakh has mentioned the data however is not in separate columns.

What PIVOT code would I use to make the 5 attribute id's as my column headers? and then just populate the table with attribute values?

Thanks guys really appreciate this.

Kind Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 02:32:59

quote:
Originally posted by crh88

Thanks James the formula shredded the data :)

As Visakh has mentioned the data however is not in separate columns.

What PIVOT code would I use to make the 5 attribute id's as my column headers? and then just populate the table with attribute values?

Thanks guys really appreciate this.

Kind Regards,

see below illustration


DECLARE @x XML = '
<model-responses>
  <model mh="0x30061c0">
    <attribute id="0x5006e">one</attribute>
    <attribute id="0x50030" />
    <attribute id="0x33000e">two</attribute>
    <attribute id="0x22564">three</attribute>
    <attribute id="0xhhf0002e" error="NoSuchAttribute" />
  </model>
  <model mh="0x30006c0">
    <attribute id="0x5006e">four</attribute>
    <attribute id="0x50030">five</attribute>
    <attribute id="0x33000e">six</attribute>
    <attribute id="0x22564">seven</attribute>
    <attribute id="0xhhf0002e" />
  </model>
</model-responses>'



SELECT [0x5006e],[0x50030],[0x33000e],[0x22564],[0xhhf0002e]
FROM
(
SELECT
   DENSE_RANK() OVER (ORDER BY c1.value('../@mh','varchar(32)') DESC) AS Rn,
	c1.value('./@id','varchar(32)') AS AttributeId,
	c1.value('.','varchar(32)') AS AttributeValue
FROM
	@x.nodes('/model-responses/model/attribute') T1(c1)
	)t
	PIVOT(MAX(AttributeValue) FOR AttributeId IN ([0x5006e],[0x50030],[0x33000e],[0x22564],[0xhhf0002e]))P



output
---------------------------------------------------
0x5006e	0x50030	0x33000e	0x22564	0xhhf0002e
---------------------------------------------------
one		two	        three	
four	five	six	        seven

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

crh88
Starting Member

14 Posts

Posted - 2014-01-17 : 04:55:13

Works nicely :) Many thanks.

Sorry - one more thing I need.

How would I name the columns

0x5006e - Column1
0x50030 - Column2
0x33000e - Column3
0x22564 - Column4
0xhhf0002e - Column5

As part of the above code?

Many thanks indeed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 07:00:21

quote:
Originally posted by crh88

Works nicely :) Many thanks.

Sorry - one more thing I need.

How would I name the columns

0x5006e - Column1
0x50030 - Column2
0x33000e - Column3
0x22564 - Column4
0xhhf0002e - Column5

As part of the above code?

Many thanks indeed.

like this


DECLARE @x XML = '
<model-responses>
  <model mh="0x30061c0">
    <attribute id="0x5006e">one</attribute>
    <attribute id="0x50030" />
    <attribute id="0x33000e">two</attribute>
    <attribute id="0x22564">three</attribute>
    <attribute id="0xhhf0002e" error="NoSuchAttribute" />
  </model>
  <model mh="0x30006c0">
    <attribute id="0x5006e">four</attribute>
    <attribute id="0x50030">five</attribute>
    <attribute id="0x33000e">six</attribute>
    <attribute id="0x22564">seven</attribute>
    <attribute id="0xhhf0002e" />
  </model>
</model-responses>'



SELECT [0x5006e] AS Column1,
[0x50030] AS Column2,
[0x33000e] AS Column3,
[0x22564] AS Column4,
[0xhhf0002e]  AS Column5
FROM
(
SELECT
   DENSE_RANK() OVER (ORDER BY c1.value('../@mh','varchar(32)') DESC) AS Rn,
	c1.value('./@id','varchar(32)') AS AttributeId,
	c1.value('.','varchar(32)') AS AttributeValue
FROM
	@x.nodes('/model-responses/model/attribute') T1(c1)
	)t
	PIVOT(MAX(AttributeValue) FOR AttributeId IN ([0x5006e],[0x50030],[0x33000e],[0x22564],[0xhhf0002e]))P

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

crh88
Starting Member

14 Posts

Posted - 2014-01-17 : 15:56:54

Many Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-18 : 02:40:55

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources