Question

-VB- on Mon, 07 Oct 2013 07:04:19


Hello,

I have XML which is stored in this format:

create table #test
(
info xml
);

insert into #test
select 
'<?xml version="1.0" encoding="utf-8"?>
<PacketValuesCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Id>SomeData</Id>
  <Packet>
    <PacketItem xsi:type="PacketValuesCollection">
      <Id>SomeValues</Id>
      <Packet>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Id</Id>
          <Value>1</Value>
        </PacketItem>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Name</Id>
          <Value>John</Value>
        </PacketItem>
      </Packet>
    </PacketItem>
  </Packet>
</PacketValuesCollection>'

How to write correctly query from this table? The only query which returns not NULL result is 

select info.value('PacketValuesCollection[1]','varchar(200)')
from #test

How to return Manager's ID?

The query 

select info.value('Manager_Id[1]','varchar(200)')
from #test

returns NULL


Sponsored



Replies

Sugumar Pannerselvam on Mon, 07 Oct 2013 07:39:21


Hi,

Try the attached sample.

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'
SELECT
a.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,
a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,
a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,
a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ’+
+a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,
a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,
a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,
a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,
a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,
a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b) 

- Pls mark as answer/vote, if this post is helpful

Sugumar Pannerselvam

 

 

-VB- on Mon, 07 Oct 2013 08:03:40


Hi,

I edited your code for my case. Tried some cases but all don't return any result..

DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0" encoding="utf-8"?>
<PacketValuesCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Id>SomeData</Id>
  <Packet>
    <PacketItem xsi:type="PacketValuesCollection">
      <Id>SomeValues</Id>
      <Packet>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Id</Id>
          <Value>1</Value>
        </PacketItem>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Name</Id>
          <Value>John</Value>
        </PacketItem>
      </Packet>
    </PacketItem>
  </Packet>
</PacketValuesCollection>'

SELECT
a.b.value('Packet[1]/Packet[1]','varchar(10)') AS Value1,
a.b.value('ID[1]/ID[1]','varchar(10)') AS Value2
FROM @MyXML.nodes('PacketValuesCollection') a(b) 

SELECT
a.b.value('Packet[1]/Packet[1]','varchar(10)') AS Value1,
a.b.value('ID[1]/ID[1]','varchar(10)') AS Value2
FROM @MyXML.nodes('Packet') a(b) 

SELECT
a.b.value('Packet[1]/Packet[1]','varchar(10)') AS Value1,
a.b.value('ID[1]/ID[1]','varchar(10)') AS Value2
FROM @MyXML.nodes('PacketItem') a(b) 



Sugumar Pannerselvam on Mon, 07 Oct 2013 08:59:29


Hi,

Attached the edited code for your scenario. Just copy and run the same.

DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0" encoding="utf-8"?>
<PacketValuesCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Id>SomeData</Id>
  <Packet>
    <PacketItem xsi:type="PacketValuesCollection">
      <Id>SomeValues</Id>
      <Packet>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Id</Id>
          <Value>1</Value>
        </PacketItem>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Name</Id>
          <Value>John</Value>
        </PacketItem>
      </Packet>
    </PacketItem>
  </Packet>
</PacketValuesCollection>'
SELECT Cust.value('(Id)[1]', 'VARCHAR(100)') AS 'PacketCollectionID'
FROM @MyXML.nodes('/PacketValuesCollection/Packet/PacketItem') AS AOC(Cust)
SELECT Cust.value('(Id)[1]', 'VARCHAR(100)') AS 'PacketItemID',
	   Cust.value('(Value)[1]', 'VARCHAR(100)') AS 'PacketItemValue'
FROM @MyXML.nodes('/PacketValuesCollection/Packet/PacketItem/Packet/PacketItem') AS AOC(Cust)

- Pls mark as answer/vote, if this post is helpful.

Sugumar Pannerselvam

-VB- on Mon, 07 Oct 2013 11:44:41


I marked as answer because the result is the same as I expected :)

I have additional question. How the query should be like if XML columns would be table's column.

I mean like that:

create table managers (

country varchar(200),

manager XML

);



insert into managers values ('Germany', 
'<?xml version="1.0" encoding="utf-8"?>
<PacketValuesCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Id>SomeData</Id>
  <Packet>
    <PacketItem xsi:type="PacketValuesCollection">
      <Id>SomeValues</Id>
      <Packet>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Id</Id>
          <Value>1</Value>
        </PacketItem>
        <PacketItem xsi:type="PacketValue">
          <Id>Manager_Name</Id>
          <Value>John</Value>
        </PacketItem>
      </Packet>
    </PacketItem>
  </Packet>
</PacketValuesCollection>')



Kalman Toth on Mon, 07 Oct 2013 11:46:06


I am moving it to XML.

Sugumar Pannerselvam on Mon, 07 Oct 2013 11:50:53


Hi,

Pls refer this forum.

MSDN - Import XML into Table

- Pls mark as answer/vote, if this post is helpful

Sugumar Pannerselvam

Stefan Hoffmann on Mon, 07 Oct 2013 12:22:33


Huh? E.g.

DECLARE @test TABLE ( info XML );

INSERT  INTO @test
VALUES  ( '<?xml version="1.0" encoding="utf-8"?>
<PacketValuesCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<Id>SomeData</Id>
	<Packet>
		<PacketItem xsi:type="PacketValuesCollection">
			<Id>SomeValues</Id>
			<Packet>
				<PacketItem xsi:type="PacketValue">
					<Id>Manager_Id</Id>
					<Value>1</Value>
				</PacketItem>
				<PacketItem xsi:type="PacketValue">
					<Id>Manager_Name</Id>
					<Value>John</Value>
				</PacketItem>
			</Packet>
		</PacketItem>
	</Packet>
</PacketValuesCollection>
' );

SELECT  PacketValuesCollection.query('.') ,
        PacketValuesCollection.value('(//PacketItem[Id="Manager_Id"]/Value)[1]', 'NVARCHAR(255)') AS ManangerID ,
        PacketValuesCollection.value('(//PacketItem[Id="Manager_Name"]/Value)[1]', 'NVARCHAR(255)') AS ManangerName
FROM    @test T
        CROSS APPLY T.info.nodes('/PacketValuesCollection') PacketValuesCollections ( PacketValuesCollection );

btw, that XML's structure is pretty interesting... looks like a hierarchy one.

-VB- on Mon, 07 Oct 2013 17:43:34


Thanks for the answer.