SQLTeam.com | Weblogs | Forums

Parsing XML

I want to extract some values from the XML column (below) in SQL.

SizeA 146 SizeB 266.7 Tolerance 0.651 Overall 0.695 Average 0.738 Can 283.7 Length 875 Bing 410.4

I want to get the values for SizeA, SizeB, Average, Can, Length`

Use a string splitter TVF:

SELECT MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE '' END) AS SizeA
	,MAX(CASE WHEN ItemNumber = 4 THEN Item ELSE '' END) AS SizeB
	--etc
FROM dbo.DelimitedSplit8K('SizeA 146 SizeB 266.7 Tolerance 0.651 Overall 0.695 Average 0.738 Can 283.7 Length 875 Bing 410.4', ' ');

i think the formatting was lost. make sure to post your xml with the proper tick in front and after the xml

this tick ->> `

3 in the front of xml 3 in the end

image

I want to extract some values from the XML column (below) in SQL.
I want to get the values for size, time, Job Number, Target, Angle
image

It seems the code did not appear well, please find the right one below.
Thanks for your help
image

please post not as an image but as a usable xml. as recommended use 3 ticks

->> `

before and after the xml

Is this what you are looking for??

declare @x xml = '<KeyValuePair>
	<Key>size</Key>
	<Value>12</Value>
</KeyValuePair>
<KeyValuePair>
	<Key>time</Key>
	<Value>2021/09/18 06:54:00 PM</Value>
</KeyValuePair>
<KeyValuePair>
	<Key>Job Number</Key>
	<Value>10</Value>
</KeyValuePair>
<KeyValuePair>
	<Key>Target</Key>
	<Value>0.0000</Value>
</KeyValuePair>
<KeyValuePair>
	<Key>Angle</Key>
	<Value>400.0000</Value>
</KeyValuePair>'


 SELECT  fields.value('Key[1]', 'varchar(30)') AS [Key],
		fields.value('Value[1]', 'varchar(30)') AS [Value]
FROM	@x.nodes('//KeyValuePair') as  xmldata(fields)
1 Like
declare @xmlDocument xml = '<root><KeyValuePair>
	<Key>Size</Key>
	<Value>12</Value>
</KeyValuePair>	
<KeyValuePair>
	<Key>JUUU</Key>
	<Value>15</Value>
</KeyValuePair></root>'

--select  X.Y.value('(Key)[1]', 'VARCHAR(20)') as BankName,
--        X.Y.value('(Value)[1]', 'int') as Value
-- from @xmlDocument.nodes('keyValuePair') as x(y)

DECLARE @docHandle int;
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;  

SELECT [Key], Value
FROM OPENXML(@docHandle, 'root/KeyValuePair')
WITH 
(
	[Key] [varchar](100) 'Key',
	Value [varchar](100) 'Value'
)

EXEC sp_xml_removedocument @docHandle
select 
    tbl.items.value('Key[1]', 'varchar(100)') AS [Key],
    tbl.items.value('Value[1]', 'varchar(100)') AS Value
FROM @xmlDocument.nodes('/root') AS a(b)
CROSS APPLY a.b.nodes('KeyValuePair') AS tbl(items);

SELECT  fields.value('Key[1]', 'varchar(30)') AS [Key],
		fields.value('Value[1]', 'varchar(30)') AS [Value]
FROM	@xmlDocument.nodes('//KeyValuePair') as  xmldata(fields)