Parsing XML

1

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`

2

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', ' ');
3

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

7

image

8

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

9

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

10

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

->> `

before and after the xml

11

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
12 
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)